Forum Discussion
Overlap tables and extract a month column from the first row.
Hi,
I receive each week a table with this structure:
| week 1 | week 2 | |||||
| Applications | ok | ko | Applications | ok | ko | |
| application 1 | 50 | 40 | application 1 | 35 | 40 | |
| application 2 | 35 | 70 | application 2 | 25 | 70 | |
| application 3 | 42 | 35 | application 3 | 89 | 35 |
I would like to create a table with columns: "week" | "application"| "ok" | "ko"
So, 2 difficulties (for me):
-> create a column "Week" with the week's number from the first line
-> "merge" the 2 tables
Could you help me?
tks!
Claire
-
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
5 Replies
- ArwaAldoud
Super User
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
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
DiKi-IPost Partisan
Do you receive any date column/ on which date it is received?
hi,
no colum with date, just the period on the first line (and in many export I have the same issue).
But it's ok with answer above.
Tks!
