Grouping Pivoting and determining current state

Question

I'm a little baffled.&nbsp; &nbsp;I'm ok with basic transformations, but this is a little beyond my experience.&nbsp; Your assistance is greatly appreciated.&nbsp;I have data that looks like this.&nbsp;&nbsp;ClientComponentSubComponent&nbsp;PercentCompleteClient 1ItemAPhase175Client 1ItemAPhase240Client 1ItemAPhase30Client 1ItemBPhase1100Client 1ItemBPhase2100Client 1ItemBPhase3100Client 2ItemAPhase1100Client 2ItemAPhase2200Client 2ItemAPhase310Client 2ItemBPhase110Client 2ItemBPhase20Client 2ItemBPhase30&nbsp;And I need transform it into something that looks like this (where Status is the most advanced phase where&nbsp; Percent &gt; 0 or Status = Complete if all phases are 100 Percent Complete)&nbsp;CientComponentStatusClient1ItemAPhase2Client1ItemBCompleteClient2ItemAPhase3Client2ItemBPhase1&nbsp;So that I can present the data to my executives in a table/matrix that&nbsp; looks like this&nbsp;&nbsp;ItemAItemBClient1Phase2CompleteClient2Phase3Phase1&nbsp;Thank you.

tackytechtom · Accepted Answer

Hi&nbsp;Steph44&nbsp;,
&nbsp;
How about the following? 🙂
&nbsp;
Before:

&nbsp;
After:

&nbsp;
Here the code in Power Query M that you can paste into the advanced editor (if you do not know, how to exactly do this, please check out this quick walkthrough)
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs7JTM0rUTBU0lHyLEnNdQTSARmJxakgAXNTpVgd3EqMgAwTA7xKjIEMrCqckO0xNMCvxogINcaYaoyw+YmQGpCAEQE1ELuwKUHzFl4lRuhhg6ECGnqxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Client = _t, Component = _t, SubComponent = _t, PercentComplete = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Client", type text}, {"Component", type text}, {"SubComponent", type text}, {"PercentComplete", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([PercentComplete] &lt;&gt; 0)),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Client", "Component"}, {{"MaxPhase", each List.Max([#"SubComponent"]), type nullable text}}),
    #"Merged Queries" = Table.NestedJoin(#"Grouped Rows", {"Client", "Component", "MaxPhase"}, #"Changed Type", {"Client", "Component", "SubComponent"}, "Grouped Rows", JoinKind.LeftOuter),
    #"Expanded Grouped Rows" = Table.ExpandTableColumn(#"Merged Queries", "Grouped Rows", {"PercentComplete"}, {"PercentComplete"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Grouped Rows", "Status", each if [PercentComplete] = 100 then "Complete" else [MaxPhase]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"MaxPhase", "PercentComplete"})
in
    #"Removed Columns"
&nbsp;
Let me know if this helps 🙂
&nbsp;
/Tomhttps://www.tackytech.blog/https://www.instagram.com/tackytechtom/

Forum Discussion

Grouping Pivoting and determining current state

2 Replies

Related content

Pivot issue

Pivot / Unpivot?

Fabric & Azure Data User Group Program Requirements & Benefits

Pivot or Group

Community User Group Experience

Client	Component	SubComponent	PercentComplete
Client 1	ItemA	Phase1	75
Client 1	ItemA	Phase2	40
Client 1	ItemA	Phase3	0
Client 1	ItemB	Phase1	100
Client 1	ItemB	Phase2	100
Client 1	ItemB	Phase3	100
Client 2	ItemA	Phase1	100
Client 2	ItemA	Phase2	200
Client 2	ItemA	Phase3	10
Client 2	ItemB	Phase1	10
Client 2	ItemB	Phase2	0
Client 2	ItemB	Phase3	0

Cient	Component	Status
Client1	ItemA	Phase2
Client1	ItemB	Complete
Client2	ItemA	Phase3
Client2	ItemB	Phase1