Power Query Spliting table with multiple columns into rows based on col1

[Pawel_Sz]

Hi All,

I bet this is a simple thing, but I can't find the answer to it and my search does't get me the proper response.

I need to change the source table A

Project	Dependency 1	Dependency Date 1	Dependency 2	Dependency Date 2	Dependency 3	Dependency Date 3	Dependency 4	Dependency Date 4
Project 1	Product 1	22.06.2023	Product 4	04.03.2024	..	..	..	..
Project 1	Service 2	05.11.2024	Product 2	22.06.2023	..	..	..	..
Project 2	Product 2	20.12.2023	Service 2	05.11.2024	..	..	..	..

into an output table B:

Project	Dependency	Dependency Date
Project 1	Product 1	22.06.2023
Project 1	Product 4	04.03.2024
Project 1	Service 2	05.11.2024
Project 1	Product 2	22.06.2023
Project 2	Product 2	20.12.2023
Project 2	Service 2	05.11.2024

Any hints I could do it quickly pls...

Many thanks in advance!

[#2 · Accepted solution]

@Pawel_Sz 

The query in the attached file returns the intended output.

[#3]

@Pawel_Sz 

As variant

let
    Source = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
    names = { "Dependency" ,"Dependency Date"},
    combineDependency = Table.SelectColumns( Table.AddColumn(
        Source,
        "Data",
        each #table( names,
            List.Zip( {
                List.RemoveNulls( List.Alternate( Record.FieldValues(_), 1, 1 ) ),
                List.RemoveNulls( List.Alternate( List.Skip( Record.FieldValues(_) ), 1, 1 ) )
                } )
        ), type table [Dependency = text, #"Dependency Date" = date] ),
        {"Project", "Data"} ),
    expandDependency = Table.ExpandTableColumn(
        combineDependency,
        "Data", names, names ),
    declareType = Table.TransformColumnTypes(
        expandDependency,
        {
            {"Project", type text},
            {"Dependency Date", type date}
        })
in
    declareType

[#4]

@OliverScheurich 

many thanks for addressing my request with such a speed! 

Apart from my Power Query skills improvements, I give you additional 10pts for making me practise my German language skills (as for whatever reason my system showed me all the steps in German) 🙂

Once highly appreciated and super useful.

Best

Pawel 

[#5]

Hi Sergei,
that one is also a super interesting alternative (also extra 10pts to you on making my brain get deeper into M-language). Many thanks!
After playing around with Oliver's I looked into yours and also came across sth on YT which made my eyes open broader wrt your approach.

[#6]

@Pawel_Sz you are welcome. As usual each task could be done by several ways, use one which is more suitable for you.