How to split multiple columns into multiple rows

[smathers1234]

Hi Community!

I'm hoping you can help me tackle an Excel Data Source challenge.

I have a very large Excel structured as follows. (Imagine a large matrix with 50 Workstreams -rows and 40 Teams -columns):

Parent ID	Child ID	Team	Lead	Status	Effort	Priority	Team	Lead	Status	Effort	Priority
Workstream 1	Work Package 1	ABC	Chris	Not Required	H		DEF	Joe	Required	H	1
Workstream 1	Work Package 2	ABC	Chris	Required	M	1	DEF	Joe	Required	M	2
Workstream 2	Work Package 1	ABC	Chris	Not Required	H		DEF	Joe	In Progress	M	3
Workstream 2	Work Package 2	ABC	Chris	Not Required	H		DEF	Joe	Not Required	L
Workstream 2	Work Package 3	ABC	Chris	In Progress	L	2	DEF	Joe	In Progress	L	4

and I would like to transform the data into the following format, to enable better reporting:

Parent ID	Child ID	Team	Lead	Status	Effort	Priority
Workstream 1	Work Package 1	ABC	Chris	Not Required	H
Workstream 1	Work Package 1	DEF	Joe	Required	H	1
Workstream 1	Work Package 2	ABC	Chris	Required	M	1
Workstream 1	Work Package 2	DEF	Joe	Required	M	2
Workstream 2	Work Package 1	ABC	Chris	Not Required	H
Workstream 2	Work Package 1	DEF	Joe	In Progress	M	3
Workstream 2	Work Package 2	ABC	Chris	Not Required	H
Workstream 2	Work Package 2	DEF	Joe	Not Required	L
Workstream 2	Work Package 3	ABC	Chris	In Progress	L	2
Workstream 2	Work Package 3	DEF	Joe	In Progress	L	4

Many thanks in advance for any help & guidance!

Scott

[CNENFRNL]

One more for your reference. It's, in essence, an variety of @shaowu459 's solution,

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rZC9DoIwEMdfpenMAvgCChgxaBo1cSAMjRRsUBqvZfDtvVYHhSiaONxHe//e7655ThkH0RqSxtSj0VGeynu6E/yMIRO8xLA13HQak6SqFBhMGEgF0lx/kRZeTvcKGm0AnxAfa/ZIGD80vBbuYjqL3CAgbY+1MmQjLp0EYXsv0AhanMzRL5VA3yv7o5RgQHlqsXIt3hJsOegTgr/skbaEgapBaP0AhaOg4SpfgHqSzEnGSOGA9Dpv5j7m00ZWMaFFcQM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t, #"(blank).4" = _t, #"(blank).5" = _t, #"(blank).6" = _t, #"(blank).7" = _t, #"(blank).8" = _t, #"(blank).9" = _t, #"(blank).10" = _t, #"(blank).11" = _t]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Added Custom" = Table.AddColumn(#"Promoted Headers", "Custom",
        each #table(List.Range(Table.ColumnNames(#"Promoted Headers"), 2, 5), List.Split(List.Skip(Record.ToList(_), 2), 5))
    ),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom", List.Range(Table.ColumnNames(#"Promoted Headers"), 2, 10)),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Team", "Lead", "Status", "Effort", "Priority"}, {"Team", "Lead", "Status", "Effort", "Priority"})
in
    #"Expanded Custom"

[smathers1234]

Thank you @CNENFRNL & @Fowmy .

Can I please check is your proposed solution within Power BI itself?  and the steps to deploy it.   I would be interested to see it in action as another learning opportunity.

Whilst I have @shaowu459 super helpful solution in Excel, that has unblocked me, the bonus would be to automate the solution within Power BI, or Excel (If I'm missing a trick) and perhaps even have a Power Automate wrapper around it.

Kind Regards

Scott

[shaowu459]

Glad I could help and thanks for making my post as solution😀

[Fowmy]

@smathers1234 

Paste the below code in a blank query and check the steps.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rZC9DoIwEMdfpenMAvgCChgxaBo1cSAMjRRsUBqvZfDtvVYHhSiaONxHe//e7655ThkH0RqSxtSj0VGeynu6E/yMIRO8xLA13HQak6SqFBhMGEgF0lx/kRZeTvcKGm0AnxAfa/ZIGD80vBbuYjqL3CAgbY+1MmQjLp0EYXsv0AhanMzRL5VA3yv7o5RgQHlqsXIt3hJsOegTgr/skbaEgapBaP0AhaOg4SpfgHqSzEnGSOGA9Dpv5j7m00ZWMaFFcQM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t, #"(blank).4" = _t, #"(blank).5" = _t, #"(blank).6" = _t, #"(blank).7" = _t, #"(blank).8" = _t, #"(blank).9" = _t, #"(blank).10" = _t, #"(blank).11" = _t]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Promoted Headers", {"Parent ID", "Child ID"}, "Attribute", "Value"),
    #"Extracted Text Before Delimiter" = Table.TransformColumns(#"Unpivoted Other Columns", {{"Attribute", each Text.BeforeDelimiter(_, "_"), type text}}),
    #"Grouped Rows" = Table.Group(#"Extracted Text Before Delimiter", {"Attribute"}, {{"Count", each _, type table [Parent ID=nullable text, Child ID=nullable text, Attribute=text, Value=text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Count],"Index",1)),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Parent ID", "Child ID", "Attribute", "Value", "Index"}, {"Parent ID", "Child ID", "Attribute", "Value", "Index"}),
    #"Pivoted Column" = Table.Pivot(#"Expanded Custom", List.Distinct(#"Expanded Custom"[Attribute]), "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Index"})
in
    #"Removed Columns"

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[shaowu459]

Hi, @smathers1234 , have a try. Change Table16 to the right name of your source table.

let
    Source = Excel.CurrentWorkbook(){[Name="Table16"]}[Content],
    res = List.TransformMany(Table.ToRows(Source),each List.Split(List.Skip(_,2),5),(x,y)=>List.FirstN(x,2)&y)
in
    #table({"Parent ID","Child ID","Team","Lead","Status","Effor","Priority"},res)

 

[smathers1234]

Hi,

Thank you so much for providing a solution, could you please share the exact steps to follow as I have been trying unsucessfully to replicate this so far.  @Fowmy  @shaowu459 

Thank you 

Scott

[shaowu459 · Accepted solution]

Please refer to below steps:

[smathers1234]

@shaowu459 That worked perfectly, thank you so much for spending your weekend creating instructions, super simple to follow!