Problem with pivoting table

[Mariollo]

Hello everyone,

I have a table in Power BI that looks like this:

Week Start Users New Amount

01/01/2024	9,994	1,459	109,365
08/01/2024	9,568	1,362	90,593
15/01/2024	10,392	1,549	83,131
22/01/2024	10,043	1,744	175,936

I would like to transform it into this format, where each date is a column and the row labels are "Users," "New," and "Amount":

  01/01/2024 08/01/2024 15/01/2024 22/01/2024

Users	9,994	9,568	10,392	10,043
New	1,459	1,362	1,549	1,744
Amount	109,365	90,593	83,131	175,936

Does anyone know the best way to pivot the data like this in Power BI? Any advice on DAX formulas or transformations in Power Query would be greatly appreciated!

When I am trying to use simple matrix table I am missing headers and can't specify order of my metrics

[Mariollo · Accepted solution]

Guys, forget my question.
Enabling this option solved all my issues for now:

[Mariollo]

@v-zhouwen-msft @Greg_Deckler 

The problem is that right now this file is created manually in Excel using copy/paste.

The weeks are in columns, and the attributes are in rows. I need to replicate this format in Power BI in the same type of table.

The data from the database, of course, is in the format shown in the screenshot below.

The solution you provided is theoretically fine, but I do not have control over format in attributes, for example can't format some values into percentages %.

And since you mentioned that this approach might not be ideal, do you perhaps have another solution?

[v-zhouwen-msft]

Hi @Mariollo ,

Agree with Greg_Deckler 's answer, you just need to delete the "Promoted headers" step and then click "Transpose".

Best Regards,
Wenbin Zhou

[Greg_Deckler]

@Mariollo That's a transpose, not a pivot. You can do the following although that is generally not the form that you want for data analysis in Power BI.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Vcy/CgIxDAbwV5HMAfOn6TWjL+Ai4lA6OHQSFe5O7vVt6+JByEc+fiRnuNX6OFzW+7wCwnWp89LyXLe2T8/357VCwQzExzZCElrv6N6TMZj3JEeN9oNpBy2mATVKvwnNdTi2P8eE6jKghf4xKbLygCJ7SEEHnMIoJkPXCKV8AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]),
    #"Transposed Table" = Table.Transpose(Source),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Week Start", type text}, {"01/01/2024", Int64.Type}, {"08/01/2024", Int64.Type}, {"15/01/2024", Int64.Type}, {"22/01/2024", Int64.Type}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Week Start", "Type"}})
in
    #"Renamed Columns"