I have a data table with the structure shown in the following sample MemberDateFundAdjustTotalExpMember11/25/202250.112.45-102.8Member12/6/202252.410.8-35.6Member13/25/202280.515.4-40.5Member21/8/202252.410.8-35.6Member22/14/202280.515.4-40.5Member33/25/202250.112.45-102.8 I use a table visual to get the output below, however I need some way to show months as columns and the sum as rows, tried with a matrix visual and neither worked. For the Members columns below I used a measure that calculing the distinct count of Member. Output obtained MonthMembersSum of FundsSum of AdjustSum of TotalExpJanuary2852238140697346-3370538February2923249529497758-3372914March28762497261117623-3169134April2862236665895258-2862159May2932242570391620-3485729June29932522584110785-3558581July30012457307121683-3053373August31022550751113697-3482299September3153256282582341-3144798October32062665435131396-3156058November3156241512028092-2945539 Output desired: MonthJanuaryFebruaryMarchAprilMayJuneJulyAugustSeptemberOctoberNovemberMembers28522923287628622932299330013102315332063156Sum of Funds23814062495294249726123666582425703252258424573072550751256282526654352415120Sum of Adjust973469775811762395258916201107851216831136978234113139628092Sum of TotalExp-3370538-3372914-3169134-2862159-3485729-3558581-3053373-3482299-3144798-3156058-2945539 Appreciate any help. Thanks

Forum Discussion

Anonymous

Not applicable

3 years ago

Solved

Transpose / Pivot data

I have a data table with the structure shown in the following sample

Member	Date	Fund	Adjust	TotalExp
Member1	1/25/2022	50.1	12.45	-102.8
Member1	2/6/2022	52.4	10.8	-35.6
Member1	3/25/2022	80.5	15.4	-40.5
Member2	1/8/2022	52.4	10.8	-35.6
Member2	2/14/2022	80.5	15.4	-40.5
Member3	3/25/2022	50.1	12.45	-102.8

I use a table visual to get the output below, however I need some way to show months as columns and the sum as rows, tried with a matrix visual and neither worked. For the Members columns below I used a measure that calculing the distinct count of Member.

Output obtained

Month	Members	Sum of Funds	Sum of Adjust	Sum of TotalExp
January	2852	2381406	97346	-3370538
February	2923	2495294	97758	-3372914
March	2876	2497261	117623	-3169134
April	2862	2366658	95258	-2862159
May	2932	2425703	91620	-3485729
June	2993	2522584	110785	-3558581
July	3001	2457307	121683	-3053373
August	3102	2550751	113697	-3482299
September	3153	2562825	82341	-3144798
October	3206	2665435	131396	-3156058
November	3156	2415120	28092	-2945539

Output desired:

Month	January	February	March	April	May	June	July	August	September	October	November
Members	2852	2923	2876	2862	2932	2993	3001	3102	3153	3206	3156
Sum of Funds	2381406	2495294	2497261	2366658	2425703	2522584	2457307	2550751	2562825	2665435	2415120
Sum of Adjust	97346	97758	117623	95258	91620	110785	121683	113697	82341	131396	28092
Sum of TotalExp	-3370538	-3372914	-3169134	-2862159	-3485729	-3558581	-3053373	-3482299	-3144798	-3156058	-2945539

Appreciate any help. Thanks

Need Help

Anonymous

3 years ago

Hi Anonymous ,

I have created a simple sample , please refer to my pbix file to see if it helps you.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TZE9SwNBEIb/y9UJ7HzvlDYWAbWwDCmScKgQk3DeCf57d2dOsNqFe+ad597d74fd8bocp59hM2AV7AdV4KLt5kbczy2RFaE6HDb74XE8TX8DjtQPdkHnGDCp6wA6cAw8Hafze8SbJm2o0G4AphGwJVAHSvzhPn1cAte0UdUIbUsyvH8B8TU8RShYRrHSEx0USyRzlaYS7G65jgF7WAu2PA6PYlWCFqlSYaUvPZpKgYgWo2KdRtCa1q0TMkrr5W35mjsPJVREikn+Janb6oJtefCv430eP0/jFCOSQooVu0dFYshemM2z95fzfFt5jNfB1gtT54GAPB8KRIvkwPPt+9+GrB4EohesxTG6dBah5nT4BQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Month = _t, Members = _t, #"Sum of Funds" = _t, #"Sum of Adjust" = _t, #"Sum of TotalExp" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Month", type text}, {"Members", Int64.Type}, {"Sum of Funds", Int64.Type}, {"Sum of Adjust", Int64.Type}, {"Sum of TotalExp", Int64.Type}}),
    #"Transposed Table" = Table.Transpose(#"Changed Type"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"January", Int64.Type}, {"February", Int64.Type}, {"March", Int64.Type}, {"April", Int64.Type}, {"May", Int64.Type}, {"June", Int64.Type}, {"July", Int64.Type}, {"August", Int64.Type}, {"September", Int64.Type}, {"October", Int64.Type}, {"November", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type1", "Index", 1, 1, Int64.Type)
in
    #"Added Index"

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8k3NTUotKlaK1YlWCi7NVchPU3ArzUtBEXBMySotLkEWCckvScxxrShQio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Month = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Month", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type)
in
    #"Added Index"

let
    Source = Table.NestedJoin(Table, {"Index"}, #"Table (2)", {"Index"}, "Table (2)", JoinKind.LeftOuter),
    #"Expanded Table (2)" = Table.ExpandTableColumn(Source, "Table (2)", {"Month"}, {"Month"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded Table (2)",{"Month", "January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "Index"}),
    #"Removed Columns" = Table.RemoveColumns(#"Reordered Columns",{"Index"})
in
    #"Removed Columns"

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Transpose.pbix46 KB

3 Replies

Idrissshatila
Super User
3 years ago
Hello Anonymous ,

You could reffer to this document to https://learn.microsoft.com/en-us/power-query/transpose-table

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

Follow me on Linkedin

Anonymous

Not applicable

3 years ago

Hi Idrissshatila probably the title of my question is not right, the table that I show is the result of a visual, my data table has another structure, as I commented in the text, see below a sample. So, I don't know how transpose my table to get the visual that I need, even I'm not sure if transpose is the solution.

Member	Date	Fund	Adjust	TotalExp
Member1	1/25/2022	50.1	12.45	-102.8
Member1	2/6/2022	52.4	10.8	-35.6
Member1	3/25/2022	80.5	15.4	-40.5
Member2	1/8/2022	52.4	10.8	-35.6
Member2	2/14/2022	80.5	15.4	-40.5
Member3	3/25/2022	50.1	12.45	-102.8

Anonymous

Not applicable

3 years ago

Hi Anonymous ,

I have created a simple sample , please refer to my pbix file to see if it helps you.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TZE9SwNBEIb/y9UJ7HzvlDYWAbWwDCmScKgQk3DeCf57d2dOsNqFe+ad597d74fd8bocp59hM2AV7AdV4KLt5kbczy2RFaE6HDb74XE8TX8DjtQPdkHnGDCp6wA6cAw8Hafze8SbJm2o0G4AphGwJVAHSvzhPn1cAte0UdUIbUsyvH8B8TU8RShYRrHSEx0USyRzlaYS7G65jgF7WAu2PA6PYlWCFqlSYaUvPZpKgYgWo2KdRtCa1q0TMkrr5W35mjsPJVREikn+Janb6oJtefCv430eP0/jFCOSQooVu0dFYshemM2z95fzfFt5jNfB1gtT54GAPB8KRIvkwPPt+9+GrB4EohesxTG6dBah5nT4BQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Month = _t, Members = _t, #"Sum of Funds" = _t, #"Sum of Adjust" = _t, #"Sum of TotalExp" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Month", type text}, {"Members", Int64.Type}, {"Sum of Funds", Int64.Type}, {"Sum of Adjust", Int64.Type}, {"Sum of TotalExp", Int64.Type}}),
    #"Transposed Table" = Table.Transpose(#"Changed Type"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"January", Int64.Type}, {"February", Int64.Type}, {"March", Int64.Type}, {"April", Int64.Type}, {"May", Int64.Type}, {"June", Int64.Type}, {"July", Int64.Type}, {"August", Int64.Type}, {"September", Int64.Type}, {"October", Int64.Type}, {"November", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type1", "Index", 1, 1, Int64.Type)
in
    #"Added Index"

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8k3NTUotKlaK1YlWCi7NVchPU3ArzUtBEXBMySotLkEWCckvScxxrShQio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Month = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Month", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type)
in
    #"Added Index"

let
    Source = Table.NestedJoin(Table, {"Index"}, #"Table (2)", {"Index"}, "Table (2)", JoinKind.LeftOuter),
    #"Expanded Table (2)" = Table.ExpandTableColumn(Source, "Table (2)", {"Month"}, {"Month"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded Table (2)",{"Month", "January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "Index"}),
    #"Removed Columns" = Table.RemoveColumns(#"Reordered Columns",{"Index"})
in
    #"Removed Columns"

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Transpose.pbix46 KB

Forum Discussion

Transpose / Pivot data

3 Replies

Related content

Pivot/ transpose

Help Transposing

Transpose data

Transpose & Pivot using Power Query

Pivoting/transposing problem