Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Jukie
Frequent Visitor

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?

 

Jukie_0-1720648219125.png

THANKS!

1 ACCEPTED SOLUTION
ryan_mayu
Super User
Super User

@Jukie 

maybe you can try this

merge same group of column

11.PNG

 

12.PNG

 

select the first two columns and unpivot other columns

 

13.png

 

remove the attribute column and split value column by delimiter

14.PNG





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

6 REPLIES 6
ryan_mayu
Super User
Super User

@Jukie 

maybe you can try this

merge same group of column

11.PNG

 

12.PNG

 

select the first two columns and unpivot other columns

 

13.png

 

remove the attribute column and split value column by delimiter

14.PNG





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




@ryan_mayu  Exactly the result I was hoping to get.  Thank you for taking the time to think this over and provide an answer.  The images and detailed directions are super helpful.  THANKS!

you are welcome





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




lbendlin
Super User
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-...

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/1447...

@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          
ConferencePlannerPayment Method1Vendor1Expense Type1Total Cost1CONCAT1Payment Method2Vendor2Expense Type2Total Cost2CONCAT2Payment Method3Vendor3Expense Type3Total Cost3CONCAT3
Big ConferenceGeorgeCredit CardHotelRoom Rental $ 1,000.00 Credit Card * Hotel * Room Rental * 1000 Credit CardRestaurantF&B $       500.00 Credit Card * Restaurant * F&B * 500 Wire TransferCar CompanyTransportation $       120.00 Wire Transfer * Car Company * Transportation * 120 
THE ConferenceSallyCashRestaurantF&B $     300.00 Credit CardConference CenterRoom Rental $   2,000.00 VenmoEntertainer XEntertainment $       300.00 
                 
Unpivot End Result              
ConferencePlannerPayment MethodVendorExpense TypeCost           
Big ConferenceGeorgeCredit CardHotelRoom Rental $ 1,000.00           
Big ConferenceGeorgeCredit CardRestaurantF&B $     500.00           
Big ConferenceGeorgeWire TransferCar CompanyTransportation $     120.00           
THE ConferenceSallyCashRestaurantF&B $     300.00           
THE ConferenceSallyCredit CardConference CenterRoom Rental $ 2,000.00           
THE ConferenceSallyVenmoEntertainer XEntertainment $     300.00           

 

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.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.