Power Query issues in SSAS using Visula Studio 2019

[anandav]

Hi,

I have used Power Query designer in Power BI for a few years now. Now I am trying to use it in SSAS (and possibly SSIS in near future).

I am using Visual Studio 2019 to create a SSAS project (compatibility level 1500)  for SQL 2019. I can import data from SQL 2019 table/views and can perform transformation in Power Query at the time of data source setup in SSAS project in VS2019.

My questions:

1. When doing transformations using Power Query when setting up a data source and loading data, the data does not immediately appear in the model.bim tab. I need to close the widnow and open again to make the data appear for the table in model.bim tab.

Any idea why this is?

2. How do I access Power Query in SSAS after data import?

I tried to find the button to do this but only way I can access is via Table Properties -> click 'Design...' button. But I can only see the selected table in the query. Unlike in Power BI in the Query Designer/Editor I can see all data import queries.

3. As stated in step#2, after I import data, when I added a custom column some it is not visible in the model.bim window. It has also disappeared from the script.  Why is this?

4. Further to step#3, sometimes the custom column (added via Power Query post data import) is visible in model.bim, but the transformed data for that column is not visible. But after deploying the model to SSAS, when I access the model from Power BI or Excel the custom column values are visible. 

Why is the data for custom column added via Power Query not visible in Visual Studio project?

A link from 2016 about SQL Server vNext CTP1.1 (thanks to Will at MSDN forum), it seems the feature hasn't evolved since then. Or am I missing some information as I could not find any training/tutorials/blogs on how to use Power Query with SSAS projects in Visual Studio. 

Any help will be greatly appreciated.

Thanks,

Anand

[dax · Accepted solution]

Hi @anandav, 

I test this in my environment (VS 2017, I don't install the VS2019 currently). 

Q1 : I test this by using Excel  file, I find that when I connect to Excel, the data won't show in bim model, only when I click it to import new table, then it will show in bim model. sometimes it will show slowly, I think tis might be cuased by large data. You could wait or click in other blank space to see whether it work or not.

Q2: Yes, in VS, it seems that it will only open one table's query editor at once, it can't show all tables in query editor, it seems to be default.

Q3 and Q4: After you change something by Query Editor, you need to click "Process Table" in tool bar above bim windows, then it will apply modification. As I know, when deploy to server, the model will be processed, so, you could see it in server or in powerbi.

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[anandav]

Thanks a lot @dax for checking this.

#3 and #4, as you mentioned after I process the table the values are appearing. 😀