how to merge tables of different rows and same columns to one entire hierarchy matrix in power bi

[danielsun]

Greetings, I have multiple talbes which owns the same columns except for the first column which can be regarded as row. 
like:

category_1	calculation_1	calculation_2	calculation_3	calculation_4
sub_category_1_1
sub_category_1_2
sub_category_1_3

category_2	calculation_1	calculation_2	calculation_3	calculation_4
sub_category_2_1
sub_category_2_2
sub_category_2_3

Note: category_1 is different with category_2, sub_category_1_1 is also different with sub_category_2_1. And data of these tables comes from the same one table, we can call it as "Source_data".

How can I merge a number of these tables to one entire hierarchical matrix in power bi? 

[Anonymous]

Hi @danielsun 

You can create a blank query and input the following code to advanced editor in power query.

(a  as table)=>
 let
    #"Demoted Headers" = Table.DemoteHeaders(a),
    #"Changed Type1" = Table.TransformColumnTypes(#"Demoted Headers",{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type1", each not Text.StartsWith([Column1], "category")),
    #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{"Column1", "Category"}, {"Column2", "calculation_1"}, {"Column3", "calculation_2"}, {"Column4", "calculation_3"}, {"Column5", "calculation_4"}})
in
    #"Renamed Columns"

It will return a funcrion, then select the table.

It will return the tables like the following format

Then you can append these tables.

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[danielsun]

In your solution, you mean to union every generated tables, correct?

[Anonymous]

Hi @danielsun 

Yes, generate it.

Best Regards!

Yolo Zhu

[danielsun]

yeah, that is a way to get that. But what I worry about is my source_data table has 10K records, and each month it will be raised by 5K records. Also, I actually have 34 tables like above. Do you think that is the only way to achieve? Thanks for your reply

[Anonymous · Accepted solution]

Hi @danielsun 

Based on your description, it is better that you can put them into a folder so that you can merge them at one time, you can refer to the following link.

Combine Multiple or All Sheets from an Excel File into a Power BI solution Using Power Query Dynamic...

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.