Dynamically rename the columns when multiple different table data stored in one table

[vinothkumar1990]

Hi,

  There is a stange design in my project where as all the tables data stored in one table (i.e. Order and Product table in one table). Below are the example,

Column Name Mapping Table: In this table all the table and columns mapping are provided. Column Name and Table Name are primary key.

Data Dump Table: All the table data dumped in this table. In this example Order and Product table data stored in one place. 

My requirement is i want to display the table matrix for all the table with target columns name like below.

There are 200+ tables are there. I don't want to rename the columns manually also i don't want to use the bookmark as i need to create 200+ bookmark whcih is tedious to manage. Is there anyway to do it?

Thanks in advance!

[PhilipTreacy · Accepted solution]

Hi @vinothkumar1990 

Download sample PBIX file with dummy data

I don't know where you get the 200 tables from, but my approach woudl be to load them into PBI then append them all together to form 1 table (Home tab -> Append Queries -> Append as New)

Rename the query Orders.

Then duplicate that query and rename the new query Products.

Now all you have to do is filter the Table Name column appropriately.  Remove Orders from the Products table, and remove Products from the Orders table.

Then delete any columns you don't want, sort, and finally rename columns.  With this approach there's only 7 columns to rename and you only do it once.

Orders Query and Table

let
    Source = Table.Combine({Table3, Table2, Table1}),
    #"Filtered Rows" = Table.SelectRows(Source, each ([Table Name] = "Order")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Column5", "Column6", "Column7", "Table Name"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Column1", "Order ID"}, {"Column2", "Order Date"}, {"Column3", "Qty"}, {"Column4", "Amount"}}),
    #"Sorted Rows" = Table.Sort(#"Renamed Columns",{{"Order ID", Order.Ascending}})
in
    #"Sorted Rows"

Products Query and Table

let
    Source = Table.Combine({Table3, Table2, Table1}),
    #"Filtered Rows" = Table.SelectRows(Source, each ([Table Name] = "Product")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Column4", "Column5", "Column6", "Column7", "Table Name"}),
    #"Sorted Rows" = Table.Sort(#"Removed Columns",{{"Column1", Order.Ascending}}),
    #"Renamed Columns" = Table.RenameColumns(#"Sorted Rows",{{"Column1", "Product ID"}, {"Column2", "Product Name"}, {"Column3", "Product Desc"}})
in
    #"Renamed Columns"

Regards

Phil