Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
I have a syntax question around a source query I'm trying to create.
I have a master query that connects to several company databases from Business Central and pulls back all the tables and when then I am using references against that query to pull out the data I need for the different companies.
So the Source Query looks like this
When referencing, I want to read from a single table but for all companies.
If I exclue the data from the query and just have a source pointing to the table object like this. I can see the Company ID and Company Name. If actually include the data element and write the source as = BCData{[Name="Projects_BI_PGS"]}[Data] or just expande the data as an additional step I lose the information about the company the data came from.
Is there a way I can re-phrase this syntax = BCData{[Name="Projects_BI_PGS"]}[Data] to include the Company ID and Company against each record from the table?
P.S. I am aware I could do it in multiple steps referencing the data filtering to the table and expading the fields like this
let
Source = BCData,
#"Filtered Rows" = Table.SelectRows(Source, each ([Name] = "Projects_BI_PGS")),
#"Expanded Data" = Table.ExpandTableColumn(#"Filtered Rows", "Data", {"No_", "Description", "Bill_to_Customer_No_", etc...})
in
#"Expanded Data"
but I was looking for a more elegant solution.
Power Query only supports dynamic sources if the eventual result (the table handed over to Power BI) has the same meta data structure. Your premise seems to be far removed from that.