Forum Discussion
Dynamic combine tables from existing tables with table(Query) name
Hi Experts,
Objective: I want to combine the existing tables in one tables with additional column as tables names in new table.
I have 2 tables Table11 and Table22 with same data Column A,B,C,D,E (Refer below Images) and wanted to combine. and want result with additional column as tables name
Table 11
Table22
Expected Result: (This is my expectation to get as a result)
Actual Result coming in Power BI:
Below Query used:
let
Source = #table({"TableNames"}, {{Record.ToTable(#sections[Section1])}}),
#"Expanded TableNames" = Table.ExpandTableColumn(Source, "TableNames", {"Name", "Value"}, {"Name", "Value"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded TableNames", each ([Name] = "Table11" or [Name] = "Table22")),
#"Expanded Value" = Table.ExpandTableColumn(#"Filtered Rows", "Value", {"A", "B", "C", "D", "E"}, {"Value.A", "Value.B", "Value.C", "Value.D", "Value.E"})
in
#"Expanded Value"
My ISSUE: I can see the data in Query editor but it is not showing in power BI Please Help. I do not want to use DAX.
This is not a bug Anonymous - it is a design issue. The service is specifically designed not to allow the #shared parameter that Power Query in Excel does. I don't know why, but it may be security or performance related or both.
You can read more about it here if you want the details -Automatically create function record for Expression.Evaluate in Power BI and Power Query – The BIccountant
I have tried the custom functin by ImkeF but it is returning null, so either I am doing something wrong (I give this a 99.9% chance of being issue here 😁) or this trick I do in Excel, which Imke showed me years ago on this very form, simply doesn't work in Power BI.
11 Replies
Anonymous , I am assuming you have appended both tables as power query and you do not see any error on the columns. And data loaded properly- After save and apply?
You want this pattern I think Anonymous
The code is this:
let Source = #table({"Source Table"}, {{"tblCars"}, {"tblComputers"}, {"tblFurniture"}}), #"Added Expression Evaluate" = Table.AddColumn(Source, "Table Names", each Expression.Evaluate([Source Table],#shared)), #"Expanded Table Names" = Table.ExpandTableColumn(#"Added Expression Evaluate", "Table Names", {"Item", "Cost", "Date Added"}, {"Item", "Cost", "Date Added"}), #"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Names",{{"Source Table", type text}, {"Item", type text}, {"Cost", Currency.Type}, {"Date Added", type date}}) in #"Changed Type"HI amitchandak
Thank you for response , but it is still not woring and Issue remain same data visible in Query Editor but not in Power BI. 😔
Below code in use
let
Source = #table({"Source Table"}, {{"Table11"}, {"Table22"}}),
#"Added Expression Evaluate" = Table.AddColumn(Source, "Table Names", each Expression.Evaluate([Source Table],#shared)),
#"Expanded Table Names" = Table.ExpandTableColumn(#"Added Expression Evaluate", "Table Names", {"A", "B", "C", "D", "E"}, {"Table Names.A", "Table Names.B", "Table Names.C", "Table Names.D", "Table Names.E"}),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Names",{{"Table Names.A", Currency.Type}, {"Table Names.B", Int64.Type}, {"Table Names.C", Int64.Type}, {"Table Names.D", Int64.Type}, {"Table Names.E", Int64.Type}})
in
#"Changed Type"My Power BI Version is: Version: 2.100.684.0 64-bit (December 2021)
I'm looking into this Anonymous - this works fine in Excel loading it to the Excel spreadsheet as a table, or to the Excel Power Pivot Data Model, but it won't load in Power BI - I just get two rows of table names and the rest of the data is missing. This might be a bug...
Hi Anonymous ,
Could reproduce it in my side as far as my test with the same query. It works in Excel but shows blank in Power BI Desktop.
Have submited this issue internal to confirm(ICM: 279601327), would update here as soon as possible if there is any update about this issue.
Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.v-yingjl Sure, Thanks
This is not a bug Anonymous - it is a design issue. The service is specifically designed not to allow the #shared parameter that Power Query in Excel does. I don't know why, but it may be security or performance related or both.
You can read more about it here if you want the details -Automatically create function record for Expression.Evaluate in Power BI and Power Query – The BIccountant
I have tried the custom functin by ImkeF but it is returning null, so either I am doing something wrong (I give this a 99.9% chance of being issue here 😁) or this trick I do in Excel, which Imke showed me years ago on this very form, simply doesn't work in Power BI.
