Forum Discussion
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 my list and it gets pulled into Power BI that way.
I know I can unpivot it when I Transform the data, but want to unpivot such that any column with a 1 is pivoted together and the same for 2, 3, etc. Could I concatenate the groups, unpivot and then split them apart? Is there an easier/better way?
THANKS!
6 Replies
- lbendlin
Super User
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information or anything not related to the issue or question.
If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523lbendlin 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
let 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.
