Forum Discussion
Dynamic combine tables from existing tables with table(Query) name
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.
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...