Re-arrange table rows and columns

[Anonymous]

Hi!

I am looking to re-arrange the following table by chnaging rowa and columns as in the example below.

Original data output :

date                 Col 1     Col 2     Col 3

2020/02/02       7           28          56

The new format of the table should be like below:

                 Date  2020/02/02

Col1                         7

Col 2                      28

Col 3                      56

Is there a way to do it. Greatly appreciate your help.

Thanks

[v-gizhi-msft · Accepted solution]

Hi,

For your requirement, i think it can be easily reached by using matrix visual.

Here is my test table and calculated columns:

Col 1 = DATEDIFF('Table'[Date],DATE(2020,2,9),DAY)
Col 2 = 'Table'[Col 1]*4
Col 2 = 'Table'[Col 1]*8

Then please try to create a table by Enter Data as below:

Then try this measure:

Measure = 
SWITCH (
    SELECTEDVALUE ( Category[.] ),
    "Col 1", MAX ( 'Table'[Col 1] ),
    "Col 2", MAX ( 'Table'[Col 2] ),
    "Col 3", MAX ( 'Table'[Col 3] )
)

Choose above new column and this measure as a matrix visual, the result shows:

Here is my test pbix file:

pbix 

Hope this helps.

Best Regards,

Giotto Zhi

[femcoldt]

Hi, 

i have a simlar situation, i have data in excel 

How can i replicate this in power bi, i have tried every approach i know but i am not getting close to this

[edhans]

Yes. In Power Query, do the following:

1. On the Home ribbon, select the dropdown for Use First Row as Headers and select the "Use Headers as First Row"
2. On the Transform tab, select Transpose
3. Back to the home ribbon, this time select Use First Row as Headers."

This table...

becomes this table...

Copy and paste this code into a Blank Query in Power Query. New, Blank Query, then hit Advanced Editor and paste this in:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ31DcyMDJQ0lEyB2ILILZUio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, #"col a" = _t, #"col b" = _t, #"col c" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"col a", Int64.Type}, {"col b", Int64.Type}, {"col c", Int64.Type}}),
    #"Demoted Headers" = Table.DemoteHeaders(#"Changed Type"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Demoted Headers",{{"Column1", type any}, {"Column2", type text}, {"Column3", type any}, {"Column4", type any}}),
    #"Transposed Table" = Table.Transpose(#"Changed Type1"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
    #"Changed Type2" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date", type text}, {"1/1/2020", Int64.Type}})
in
    #"Changed Type2"

You can see the step in action.

[Anonymous]

The column 1 , 2 nd 3 are calculated columns and not available in Query editor, so cant do that. The only coliumn availble in query editor is the Dates.

Not sure how yto handle in this scenerio.

Thanks.

[edhans]

Ideally you would redo those Calculated Columns you've done in DAX as Custom Columns in Power Query, then transpose there. Power Query is build for data modeling.

If you cannot do that, then check out this thread. As you can see, it gets really complex fast because DAX is built for analysis, not modeling. There is no "Transpose" feature to DAX natively.

If you'd like help moving these columns into Power Query, post back. Column calculations are usually best done there, not in DAX.

In general, try to avoid calculated columns. There are times to use them, but it is rare. Getting data out of the source system, creating columns in Power Query, or DAX Measures are usually preferred to calculated columns. See these references:
Calculated Columns vs Measures in DAX
Calculated Columns and Measures in DAX
Storage differences between calculated columns and calculated tables
Creating a Dynamic Date Table in Power Query