% of row total into columns

[jcastr02]

Hello I have the below data arranged by store number and total by Later, tomorrow, and waiter.  How could I rearrange to the desired result in power Query where I need the % of row total for each store.

	CURRENT					DESIRED
STORE	PRIORITY_VALUE	TTL		STORE	Later	Tomorrow	Waiter
13	Later	36627		13	61.0%	32.9%	6.0%
13	Tomorrow	19750		23	73%	18%	9%
13	Waiter	3624
23	Later	5197
23	Tomorrow	1275
23	Waiter	608

[AnushaSri · Accepted solution]

I have selected Priority_Values column and choose pivot columns

[dufoq3]

Hi @jcastr02, different approach here:

Result

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjRW0lHySSxJLQLSxmZmRuZKsTpQ4ZD83PyiovxyINPQ0tzUACETnpgJ02FkAhY2QjbHFKgcIYpsjJG5KUICboqZgYVSbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [STORE = _t, PRIORITY_VALUE = _t, TTL = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"STORE", Int64.Type}, {"TTL", type number}}),
    GroupedRows = Table.Group(ChangedType, {"STORE"}, {{"All", each 
        [ a = List.Sum([TTL]),
          b = Table.TransformColumns(_, {{"TTL", (x)=> x / a, Percentage.Type}}),
          c = Table.SelectColumns(b, {"PRIORITY_VALUE", "TTL"}),
          d = Table.PromoteHeaders(Table.FromColumns( {{"STORE", [STORE]{0}?}} & Table.ToRows(c))),
          e = Table.TransformColumnTypes(d, {{"STORE", Int64.Type}} & List.Transform([PRIORITY_VALUE], (x)=> {x, Percentage.Type}))
        ][e], type table}}),
    CombinedAll = Table.Combine(GroupedRows[All])
in
    CombinedAll

[AnushaSri]

Hi, 

Please mark this as a solution and give Kudos if it resolves your issue.

Add these steps to your Power Query to achieve what you are looking for,

#"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Priority_value]), "Priority_value", "Total"),
#"Inserted Sum" = Table.AddColumn(#"Pivoted Column", "Addition", each List.Sum({[Later], [Tomorrow], [Waiter]}), Int64.Type),
#"Inserted Percent Of" = Table.AddColumn(#"Inserted Sum", "P1", each [Later] / [Addition], Percentage.Type),
#"Inserted Percent Of1" = Table.AddColumn(#"Inserted Percent Of", "P2", each [Tomorrow] / [Addition], Percentage.Type),
#"Inserted Percent Of2" = Table.AddColumn(#"Inserted Percent Of1", "P3", each [Waiter] / [Addition], Percentage.Type),
#"Removed Columns" = Table.RemoveColumns(#"Inserted Percent Of2",{"Later", "Tomorrow", "Waiter", "Addition"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"P1", "Later"}, {"P2", "Tomorrow"}, {"P3", "Waiter"}})

[jcastr02]

@AnushaSri  Thank you!

For the first step to pivot the column - which columns did you select and which option did you pick?

Unpivot Columns

Unpivot Other Columns

Unpivot Only Selected Columns