Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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"
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
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
Glad I could help and thanks for making my post as solution😀
@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 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
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)
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
Please refer to below steps:
@shaowu459 That worked perfectly, thank you so much for spending your weekend creating instructions, super simple to follow!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.