Dynamic combine tables from existing tables with table(Query) name

[ajeetprasad]

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.

[v-yingjl]

Hi @ajeetprasad ,

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.

[ajeetprasad]

@v-yingjl  Sure, Thanks

[edhans · Accepted solution]

This is not a bug @ajeetprasad - 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 BIcco...

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.

[ImkeF]

Hi @edhans

please check the video on how to apply the function to this special use case.
Not sure though, if this all makes sense, as the basic idea to have a dynamic solution, cannot be fulfilled.

[ajeetprasad]

As there is no solution I think we can close the post. thank you for the support.

[edhans]

Ok. I'll mark mine as the "solution" for now as it is since it isn't a bug and a design issue, but if Imke comes in with some Magic M code, we can change that.

[ajeetprasad]

woring = Showing 

[edhans]

You want this pattern I think @ajeetprasad 

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"

[ajeetprasad]

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)

[edhans]

I'm looking into this @ajeetprasad - 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...

[amitchandak]

@ajeetprasad , 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?