Put financial years and labels in rows (not columns)

[Bundi009]

Hi experts,

I am desperately trying to get my financial data next to each other. In short, I want to have 4/5 columns with customer information and then show sales, quantity and costs for each line for 2021 AND 2022. Right now they are under each other,

but I want them next to each other (so 2021.Aantal, 2022.Aantal etc). Can anyone tell me the the magic trick? Thank you very much in advance!

[v-eqin-msft · Accepted solution]

Hi @Bundi009 ,

If you want this:

Please follow the steps——Unpivot-->Add column-->Remove other columns. Below is the whole M syntax:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WckzMK0nMUdJRMjYAEkYWSrE60UoBRZlZxUCuiaWeBUjKDCwakl+SmJijkJdaUpIPFDU3MQcpMbUES8LNMQSZY2iEYo6RqZ6JKcggUz2sRhmZmOiB5Q0M9IwMlWJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Attribute = _t, #"2021" = _t, #"2022" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Attribute", type text}, {"2021", type number}, {"2022", type number}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Attribute"}, "Attribute.1", "Value"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "Custom", each [Attribute.1] &"."& [Attribute]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Attribute", "Attribute.1"})
in
    #"Removed Columns"

Or if you want this:

Then use "Pivot" after the steps above.

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

[Bundi009]

Perfect - thank you!

[Anonymous]

You can select Attribute column and Unpivot the financial year columns (2021 and 2022) to get each attribute in single rows