Dynamic Table Data Mapping !!

[SNPPowerbi]

Hi,

I have mapping table, where tentant wise table & column values. I need to pull data as per the mapping table & columns to be populated on PowerBI

Any column removal/addition that should impact on report.

Sample Mapping Table

ID	Col1	Col2	Col3	Col4	Col5	Col6	Col7	Col7	Col8	Col9	Col10
1	Table1.col1	Table1.col2	Table1.col2	Table3.col4	Table1.col3	Table3.col6	Table5.col2	Table4.col4	Table6.col2	Table2.col1	Tenatant1
2	Table2.col1	Table2.col1	Table2.col1	Table2.col4	Table1.col3	Table3.col6	Table5.col2	Table4.col4	Table6.col2	Table2.col1	Tenatant2
3	Table1.col1	Table1.col2	Table1.col2	Table3.col4	Table1.col3	Table3.col6	Table5.col2	Table4.col4	Table6.col2	Table2.col1	Tenatant3
4	Table2.col1	Table2.col1	Table2.col1	Table2.col4	Table1.col3	Table3.col6	Table5.col2	Table4.col4	Table6.col2	Table2.col1	Tenatant4
5	Table1.col1	Table1.col2	Table1.col2	Table3.col4	Table1.col3	Table3.col6	Table5.col2	Table4.col4	Table6.col2	Table2.col1	Tenatant5
6	Table2.col1	Table2.col1	Table2.col1	Table2.col4	Table1.col3	Table3.col6	Table5.col2	Table4.col4	Table6.col2	Table2.col1	Tenatant6
7	Table1.col1	Table1.col2	Table1.col2	Table3.col4	Table1.col3	Table3.col6	Table5.col2	Table4.col4	Table6.col2	Table2.col1	Tenatant7
8	Table2.col1	Table2.col1	Table2.col1	Table2.col4	Table1.col3	Table3.col6	Table5.col2	Table4.col4	Table6.col2	Table2.col1	Tenatant8
9	Table1.col1	Table1.col2	Table1.col2	Table3.col4	Table1.col3	Table3.col6	Table5.col2	Table4.col4	Table6.col2	Table2.col1	Tenatant9
10	Table2.col1	Table2.col1	Table2.col1	Table2.col4	Table1.col3	Table3.col6	Table5.col2	Table4.col4	Table6.col2	Table2.col1	Tenatant10

[ImkeF]

Hi,

this should do:

let

// You can copy this function code as a standalone-function as well
fnMappgingTable= (MappingTable, NameOfFilterColumn, FilterValue) =>
let
    Source = MappingTable,
    #"Filtered Rows" = Table.SelectRows(Source, each (Record.Field(_, NameOfFilterColumn) = FilterValue)){0},
    #"Converted to Table" = Record.ToTable(#"Filtered Rows"),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Converted to Table", "Value", Splitter.SplitTextByDelimiter(".", QuoteStyle.Csv), {"Value.1", "Value.2"}),
    #"Added Custom1" = Table.AddColumn(#"Split Column by Delimiter1", "Columns", each Expression.Evaluate("Table.Column("&[Value.1]&", """&[Value.2]&""")", #shared)),
    #"Removed Errors" = Table.RemoveRowsWithErrors(#"Added Custom1", {"Columns"}),
    Custom1 = Table.FromColumns(#"Removed Errors"[Columns])
in
    Custom1,
// End of function code

    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("zZA9CoAwDEbvkrmI/a/3cCsdqnQrden9UQMKgQ4uQsbHR8jjxQgSBKx5q0VO+1EpqTHpmwzZNNncQ5bcGXLnyKbe76XlnluXkESEwf6J/rZTaKeZttNoZ5i2M2hnmbazaOeYtnNo55m282gXmLYLaLcwbbegnZyZxrvEUjoB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, Col1 = _t, Col2 = _t, Col3 = _t, Col4 = _t, Col5 = _t, Col6 = _t, Col7 = _t, #"Col7 (2)" = _t, Col8 = _t, Col9 = _t, Col10 = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Result", each fnMappgingTable(Source, "Col10", [Col10]))
in
    #"Added Custom"

The full query above returns one table per row with the different columns from the different tables. Not sure how you actually want to integrate it into your solution, but of course you can also use the function on its own so that only one table will be returned.