Importing Excel Power Pivot Model into Power BI

[chicagolarsons]

Hi All, I am looking for some help.

I have been trying to create quite an elaborate report in excel for my boss where I'm querying OLAP cubes in Power Pivot to build the bulk of the data model. After spending weeks fussing around with trying to keep pivot charts from reformatting every time you click a slicer, I got frustrated with working in Excel and looked in to importing my data model into Power BI. Lo and behold, it seemed like it was possible, so I gave it a whirl last Friday.  Literally, in a few hours, I was able to build the exact report I wanted to give my boss.  There's only one hitch, though....and it's a big one!  Here's where I need help:

After my model loaded, all my new tables had question marks on them, and gave me this error message:

I thought, no big dieal, I'll just need to re-connect to the server here in Power BI and I'll be good to go.  But after spending some time trying to figure out how to do this today, and failing multiple times,  I have discovered that it doesn't appear as though I will be able to refresh my new data model?

I can't recreate the queries in PowerBI because of the way it interacts with MS Analysis Services. Whereas in PowerPivot data modeling I can go in can select cube members and filters prior to loading the data, which does a lot to clean up the data being brought in and helps the query run efficiently. , in PowerBI, it seems like I have to load ALL the data before I can do any transforming. These files are huge, and the queries cannot complete running before they time out. (Plus, to be honest, re-doing all these queries using the "point & click" method would honestly take me forever, not to mention having to re-do all the transform work I did in excel.

I was just wondering if anyone knows if what I'm trying to do is possible, and/or the best "work around" to accomplish my goal of getting this nice data set I've developed into PowerBI so I can use it?  I'm stumped.

Thanks in advance!

Kirstin

[Anonymous · Accepted solution]

Hi @chicagolarsons ,

According to the error information you provided, have you installed the client library for connecting to Azure Analysis Services? For details, refer to the following article.

Analysis Services client libraries | Microsoft Learn

If Office is 32-bit and Desktop is 64-bit, it may cause compatibility issues. Make sure both are consistent.

Best Regards,
Wenbin Zhou

[chicagolarsons]

Thank you, this was the problem!! I never would have figured this out on my own!

[chicagolarsons]

Hi, OP here again.  I thought it might be helpful to share a copy of one of the queries that got generated automatically by power query when I imported the data model.  I'm wondering if there is a way that the query could continue to refresh in Excel and Power Query could then just copy over the updated data in the tables in the model?  

let

    Source = OleDb.Query("Provider=MSOLAP;Data Source=server name; initial catalog=BST10_Analytics_XXX;dbpropmsmdflattened2=true", "SELECT NON EMPTY { [Measures].[Balance BC], [Measures].[Budget Balance CC] } ON COLUMNS, NON EMPTY { ([Posting Calendar].[Posting Period].[Posting Period].ALLMEMBERS * [Account].[Account Type].[Account Type].ALLMEMBERS * [Account].[Company].[Company].ALLMEMBERS * [Organization].[Organization Code].[Organization Code].ALLMEMBERS * [Account].[Account Code].[Account Code].ALLMEMBERS * [Account].[Account Name].[Account Name].ALLMEMBERS * [Organization Map].[Level  2].[Level  2].ALLMEMBERS * [Account Map].[Level  5].[Level  5].ALLMEMBERS * [Account Map].[Level  6].[Level  6].ALLMEMBERS * [Account Map].[Level  7].[Level  7].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( -{ [Account].[Account Code].&[401800] } ) ON COLUMNS FROM ( SELECT ( -{ [Posting Calendar].[Posting Period].&[202513], [Posting Calendar].[Posting Period].&[202413], [Posting Calendar].[Posting Period].&[202313], [Posting Calendar].[Posting Period].&[202213], [Posting Calendar].[Posting Period].&[202113] } ) ON COLUMNS FROM ( SELECT ( { [Organization Map].[Organization Map Code].&[24Alt] } ) ON COLUMNS FROM ( SELECT ( { [Account Map].[Account Map].&[2IS: XXX Income Statement 2017] } ) ON COLUMNS FROM ( SELECT ( { [Posting Calendar].[Posting Calendar Hierarchy].[Posting Year].&[2022], [Posting Calendar].[Posting Calendar Hierarchy].[Posting Year].&[2023], [Posting Calendar].[Posting Calendar Hierarchy].[Posting Year].&[2024], [Posting Calendar].[Posting Calendar Hierarchy].[Posting Year].&[2025] } ) ON COLUMNS FROM [General Ledger]))))) WHERE ( [Posting Calendar].[Posting Calendar Hierarchy].CurrentMember, [Account Map].[Account Map].&[2IS: XXX Income Statement 2017], [Organization Map].[Organization Map Code].&[24Alt] ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS"),

    #"Renamed Columns" = Table.RenameColumns(Source, {{"[Measures].[Budget Balance CC]", "MeasuresBudget Balance CC"}, {"[Account Map].[Level  6].[Level  6].[MEMBER_CAPTION]", "Account MapLevel  6Level  6"}, {"[Account Map].[Level  7].[Level  7].[MEMBER_CAPTION]", "Account MapLevel  7Level  7"}, {"[Organization].[Organization Code].[Organization Code].[MEMBER_CAPTION]", "Org Code"}, {"[Account].[Company].[Company].[MEMBER_CAPTION]", "Company"}, {"[Account Map].[Level  5].[Level  5].[MEMBER_CAPTION]", "Account MapLevel  5Level  5"}, {"[Measures].[Balance BC]", "MeasuresBalance BC"}, {"[Posting Calendar].[Posting Period].[Posting Period].[MEMBER_CAPTION]", "BST Posting Period"}, {"[Account].[Account Type].[Account Type].[MEMBER_CAPTION]", "AcctType"}, {"[Account].[Account Code].[Account Code].[MEMBER_CAPTION]", "Acct#"}, {"[Account].[Account Name].[Account Name].[MEMBER_CAPTION]", "AccountName"}, {"[Organization Map].[Level  2].[Level  2].[MEMBER_CAPTION]", "Region"}}),

    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns", {{"MeasuresBudget Balance CC", type number}, {"Account MapLevel  6Level  6", type text}, {"Account MapLevel  7Level  7", type text}, {"Org Code", type text}, {"Company", type text}, {"Account MapLevel  5Level  5", type text}, {"MeasuresBalance BC", type number}, {"BST Posting Period", type text}, {"AcctType", type text}, {"Acct#", type text}, {"AccountName", type text}, {"Region", type text}})

in

    #"Changed Type"