cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
vinothkumar1990
Helper I
Helper I

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

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.

vinothkumar1990_0-1636948729361.png

 

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

vinothkumar1990_1-1636948769292.png

 

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

vinothkumar1990_0-1636948179240.png

 

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!

1 ACCEPTED SOLUTION
PhilipTreacy
Super User
Super User

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"

 

 

ordertab.png

 

 

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"

 

 

prodtab.png

 

 

Regards

 

Phil



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


View solution in original post

1 REPLY 1
PhilipTreacy
Super User
Super User

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"

 

 

ordertab.png

 

 

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"

 

 

prodtab.png

 

 

Regards

 

Phil



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors