Forum Discussion
Solved
Unpivot Columns in Groups
Hello! I have a Sharepoint List collecting data from a Power App Form. For end user ease, they can enter multiple expenses on one form. However, this adds all the different expenses to one row on m...
lbendlin Thank you for the directions. I had tried to attach my excel doc but it would not allow it. While I have my solution, I'll include it here just in case someone has a different idea that does not involve merging and splitting.
| Sharepoint List Data Collected from Power Apps Form | ||||||||||||||||
| Conference | Planner | Payment Method1 | Vendor1 | Expense Type1 | Total Cost1 | CONCAT1 | Payment Method2 | Vendor2 | Expense Type2 | Total Cost2 | CONCAT2 | Payment Method3 | Vendor3 | Expense Type3 | Total Cost3 | CONCAT3 |
| Big Conference | George | Credit Card | Hotel | Room Rental | $ 1,000.00 | Credit Card * Hotel * Room Rental * 1000 | Credit Card | Restaurant | F&B | $ 500.00 | Credit Card * Restaurant * F&B * 500 | Wire Transfer | Car Company | Transportation | $ 120.00 | Wire Transfer * Car Company * Transportation * 120 |
| THE Conference | Sally | Cash | Restaurant | F&B | $ 300.00 | Credit Card | Conference Center | Room Rental | $ 2,000.00 | Venmo | Entertainer X | Entertainment | $ 300.00 | |||
| Unpivot End Result | ||||||||||||||||
| Conference | Planner | Payment Method | Vendor | Expense Type | Cost | |||||||||||
| Big Conference | George | Credit Card | Hotel | Room Rental | $ 1,000.00 | |||||||||||
| Big Conference | George | Credit Card | Restaurant | F&B | $ 500.00 | |||||||||||
| Big Conference | George | Wire Transfer | Car Company | Transportation | $ 120.00 | |||||||||||
| THE Conference | Sally | Cash | Restaurant | F&B | $ 300.00 | |||||||||||
| THE Conference | Sally | Credit Card | Conference Center | Room Rental | $ 2,000.00 | |||||||||||
| THE Conference | Sally | Venmo | Entertainer X | Entertainment | $ 300.00 |
lbendlin
Super User
Super Userlet
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hVFBasMwEPzKYnoqpsguyQNi0ubshibg+rA021RgS0FWDvl9RjJJZEypQKwGaWZ2Vk2TrfSRKmt+xIn5lizP3sW6YzhUTg7aU8XuALSxXjrU2tqeajGeA6InKnKl1ItSASUUeqZIQU0oQAWe00y+lsHz2bHxAG9fZ6XK5Wo0GNfiD5MHEeBGxHExuuy0E9riekDC4MoOcfsTmwtQvDhZ59lra1K7orzZTRQgnCgATRVCvBK+bd5k2816OtgP7rpLbGH4/T/x6z3vbFYPVaow1Zhr9i1EZfIx2J9ieou6DgzP2iDNPsW9xFbuI0g6aNsr", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Conference = _t, Planner = _t, #"Payment Method1" = _t, Vendor1 = _t, #"Expense Type1" = _t, #"Total Cost1" = _t, CONCAT1 = _t, #"Payment Method2" = _t, Vendor2 = _t, #"Expense Type2" = _t, #"Total Cost2" = _t, CONCAT2 = _t, #"Payment Method3" = _t, Vendor3 = _t, #"Expense Type3" = _t, #"Total Cost3" = _t, CONCAT3 = _t]),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Conference", "Planner"}, "Attribute", "Value"),
#"Split Column by Character Transition" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"}), {"Attribute", "Sequence"}),
#"Pivoted Column" = Table.Pivot(#"Split Column by Character Transition", List.Distinct(#"Split Column by Character Transition"[Attribute]), "Attribute", "Value")
in
#"Pivoted Column"How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the Source step with your own source.