The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
Hello,
I am looking for a way to import to my Power BI Desktop data from a SQL Server Analysis Services DB filtered based on a previously imported data from another data source.
Sample scenario:
- I have an excel file with one column that contains account IDs named "ID", I import that excel file and now I have a table in my PBI named account with only one column named IDs.
- I now want to import from my Analysis Service only the accounts with IDs present in my table "account".
- The following query works very well as intended, but I want to replace the {1, 2, 3, 4} with something like account[ID] (account being my existing table in my model).
Thank you in advance.
Solved! Go to Solution.
Hi,
If you get your list imported as a table you can use the advanced power query editor to convert it to a string:
let
Source = YourExcelSource,
ChangeType= Table.TransformColumnTypes(Source,{{"Academy", type text}}),
OutputList = Table.ToList ( ChangedType ),
OutputString = "{""" & Text.Combine(OutputList,""",""") & """}"
in
OutputString
The above deals with strings hence the double "" to escape them. This returns a string which we'll reference:
If you then use advanced editor to alter your analysis service query you can reference the string like this:
If you need specific to your example I can try and rewrite.
Hi,
If you get your list imported as a table you can use the advanced power query editor to convert it to a string:
let
Source = YourExcelSource,
ChangeType= Table.TransformColumnTypes(Source,{{"Academy", type text}}),
OutputList = Table.ToList ( ChangedType ),
OutputString = "{""" & Text.Combine(OutputList,""",""") & """}"
in
OutputString
The above deals with strings hence the double "" to escape them. This returns a string which we'll reference:
If you then use advanced editor to alter your analysis service query you can reference the string like this:
If you need specific to your example I can try and rewrite.