Home Forums Power BI Synchronize Excel data model and PBIX data model

This topic contains 4 replies, has 3 voices, and was last updated by  tomallan 2 months ago.

Viewing 5 posts - 1 through 5 (of 5 total)
  • Author
    Posts
  • #8208

    cnestg8r
    Participant
    • Started: 3
    • Replies: 5
    • Total: 8

    I follow the preferred path of building the power pivot model through excel using power query, DAX studio, and the like.  I create numerous measures, a few calculated columns, and several pivot tables testing the creations.

    I then open the data model in PBID and create report visuals. Further I publish to Power BI and pin tiles to dashboards. So far, no problem.

    However as insights develop, it begs the need for some new measures, and I naturally go back to excel make the additions of interest. I am not able to update the modified excel model in PBIX. I can open a second PBIX with the modified model, but I can’t copy the visuals from the first PBIX to the second. It is true once you move to PBIX that you loose the potential to sync. This also means new measures from the PBIX side cannot be seen in excel?

    Finally, the excel 3D mapping tool is far superior to the PBIX versions. I can’t include those in Power BI or PBIX. I can create a “movie”, but how do I incorporate that into Power BI or PBIX?

    Please point me in the right direction…

    #8209

    tomallan
    Keymaster
    • Started: 9
    • Replies: 1931
    • Total: 1940

    There are different options available to move your Excel data and charts into the Power BI service. One tool you should evaluate if you have not yet installed it is the Power BI Publisher for Excel which you can download from:

    https://powerbi.microsoft.com/en-us/excel-dashboard-publisher/

    After installing the Power BI Publisher for Excel, the Pin Manager button that you will find on the Power BI tab in the Excel ribbon will be the way to keep your charts in sync.

    Tom
    http://www.powerpivotpro.com

    #8210

    cnestg8r
    Participant
    • Started: 3
    • Replies: 5
    • Total: 8

    Thank you Tom. I had not used that tool. Pinning directly to a dashboard is  a clearly a static image. This means it will reflect changes from the excel side, but cannot be used in drill downs, slicers, and the like.

    If I connect the excel data model to a dataset or report, Can I then create dynamic visuals as I would in PBIX? If that is correct, then why would one go to PBIX, unless they didn’t have Power BI access?

    #8216

    ldl_grace
    Participant
    • Started: 0
    • Replies: 1
    • Total: 1

    I have the same issue as @cnestg8r – I created a data model and measures in Excel powerpivot, imported it into PBI, and then added new measures in Excel data model. Short of a new import of the edited data model (which means re-creating all the visuals in PBI), is there a way to sync both data models in Excel and PBI?

    As suggested by @tomallan, I have installed Power BI publisher for Excel but can’t seem to find the function which syncs both data models (not charts). Could you clarify?

    Thanks!

    #8226

    tomallan
    Keymaster
    • Started: 9
    • Replies: 1931
    • Total: 1940

    To import an Excel data model (power pivot) into the Power BI service and it to stay in sync, the Excel workbook needs to be installed on a OneDrive under the same account as the Power BI service. In such a case, I believe the dataset imported from the Excel workbook refreshes automatically about once an hour. For this scenario (importing an Excel power pivot model into the Power BI service as a dataset), unless you have visuals designed in Power View, you mush re-create the visuals in the Power BI service.

    If you are using the Power BI Publisher for Excel and want to update an exported chart or graph for a report in the Power BI service, check out the Pin Manager which can be launched from the Power BI tab in Excel. For this case (using the Power BI Publisher for Excel), the Power Pivot data model is not exported to Power BI.

    I have not worked with gateways much, but you might have some options there as well.

    Overall, it seems like Excel users are presented with an either/or proposition: you can either publish (export) charts and graphs into the Power BI service without exporting the data model OR you can export the Excel data model from a OneDrive without being able to export regular Excel charts and graphs.

    Please post back if you learn something new using a local gateway.

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic.