Forum Discussion
Overlap tables and extract a month column from the first row.
Your table has duplicate header rows, making it difficult to analyze data properly.
This steps in Power Query also I Attached cleaned Power Query File below you can download it:
- Load Data into Power Query
- Use First Row as Headers. your header will be (week 1 Column2 Column3 week 2 Column5 Column6)
- Use First Row as Headers agin. Manually Rename Columns your header will be (Applications, ok_1, ko_1, Applications_2, ok_2, ko_2)
- Select the columns ok_1, ko_1, ok_2, and ko_2. → Unpivot Columns.
- Extract Week Number. → Select the "Attribute" column.Use _ (underscore) as the delimiter → Name the new column "Week".
- Attribute → Name the new column "Status"
- Delete the duplicate "Applications" column if needed.
- Pivot to Separate OK and KO Columns. Select the "Status" column.→ Pivot Column. In the Values Column, select "Value".
Cleaned Power Query File – Download Here]
If this solution was helpful, please accept it as a solution or give kudos to help other community members
Super UserYour table has duplicate header rows, making it difficult to analyze data properly.
This steps in Power Query also I Attached cleaned Power Query File below you can download it:
- Load Data into Power Query
- Use First Row as Headers. your header will be (week 1 Column2 Column3 week 2 Column5 Column6)
- Use First Row as Headers agin. Manually Rename Columns your header will be (Applications, ok_1, ko_1, Applications_2, ok_2, ko_2)
- Select the columns ok_1, ko_1, ok_2, and ko_2. → Unpivot Columns.
- Extract Week Number. → Select the "Attribute" column.Use _ (underscore) as the delimiter → Name the new column "Week".
- Attribute → Name the new column "Status"
- Delete the duplicate "Applications" column if needed.
- Pivot to Separate OK and KO Columns. Select the "Status" column.→ Pivot Column. In the Values Column, select "Value".
Cleaned Power Query File – Download Here]
If this solution was helpful, please accept it as a solution or give kudos to help other community members
Hi ArwaAldoud ,
Tks a lot, it's perfect.
Just one last question: each week I will have new table near the previous week. Do you know if we have the possibility to automate this step:
- Select the columns ok_1, ko_1, ok_2, and ko_2. → Unpivot Columns.
As we'll have ok_3, ko_3...
tks a lot
Claire
- ArwaAldoud
I’m not entirely sure about automating this fully, but you can try adding an underscore (_) at the end of "ok" and "ko" before promoting headers. This way, Power Query may dynamically name them as ok_1, ok_2, ok_3, and so on when new weeks are added.
Give it a try and let me know if it works for you.
If this was helpful give kudos to help other community members