Looking for a Method to Transform Hierarchal Data into Flat Format for Analysis

[maazbinsalman]

I am facing a problem while converting a hierarichal data from monday.com (exported to excel) to a flat table in power bi for analysis.

1. Please see the attached image in detail. The monday board has four ares listed in first column including
Estates and Facilities
Compliance.
Records/Logs and Evidence
HR

2. Under these headings we have the column headers for the data under those sections, this is same for all 4 sections but is listed under each of those

The look like

Name, Subitems, Owner.............

3. In some of the items in the Name column, we have subitems to that field. In this case the first column looks empty and subitems are in second column

Like under Health and Safety Risk Assessment

I will be using multiple sheets for different clients, more or less in a same format, What will be the best way forward.

This is my data, I want to convert it into a flat file that is good for analysis in power query editor

[v-stephen-msft]

Hi @maazbinsalman ,

Power Query is built for the data transformation.

You can do lot of things in it. For example, pivot columns, unpivot columns, merge queries, append queries and etc.

Reference:

Promote or demote column headers - Power Query | Microsoft Learn

Common Power Query transformations (50+ powerful transformations explained)

I think you'll probably need to filter out nulls, change the first row to column headers, pivot, and in-pivot.

Best Regards,
Stephen Tao

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