Overlap tables and extract a month column from the first row.

[claireDePasquie]

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

- 

[claireDePasquie]

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!

[ArwaAldoud · Accepted solution]

Hi @claireDePasquie 

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:

1. Load Data into Power Query
2. Use First Row as Headers. your header will be (week 1 Column2 Column3 week 2 Column5 Column6)
3. Use First Row as Headers agin. Manually Rename Columns your header will be (Applications, ok_1, ko_1, Applications_2, ok_2, ko_2)
4. Select the columns ok_1, ko_1, ok_2, and ko_2. → Unpivot Columns.
5. Extract Week Number. → Select the "Attribute" column.Use _ (underscore) as the delimiter → Name the new column "Week".
6. Attribute → Name the new column "Status"
7. Delete the duplicate "Applications" column if needed.
8. 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

[claireDePasquie]

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:

1. 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-I]

Do you receive any date column/ on which date it is received?