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

Copper Contributor

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

ProjectDependency 1Dependency Date 1Dependency 2Dependency Date 2Dependency 3Dependency Date 3Dependency 4Dependency Date 4
Project 1Product 122.06.2023Product 4 04.03.2024........
Project 1Service 205.11.2024Product 222.06.2023........
Project 2Product 220.12.2023Service 205.11.2024........


into an output table B:

ProjectDependency Dependency Date
Project 1Product 122.06.2023
Project 1Product 4 04.03.2024
Project 1Service 205.11.2024
Project 1Product 222.06.2023
Project 2Product 220.12.2023
Project 2Service 205.11.2024


Any hints I could do it quickly pls...

Many thanks in advance!

5 Replies
best response confirmed by Hans Vogelaar (MVP)


The query in the attached file returns the intended output.

PQ Split data.png


As variant

    Source = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
    names = { "Dependency" ,"Dependency Date"},
    combineDependency = Table.SelectColumns( Table.AddColumn(
        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(
        "Data", names, names ),
    declareType = Table.TransformColumnTypes(
            {"Project", type text},
            {"Dependency Date", type date}



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.




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.

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