Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Re-arrange table rows and columns

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    ...
Need Help
  • v-gizhi-msft's avatar
    v-gizhi-msft
    6 years ago

    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

edhans's avatar
edhans
Icon for Community Champion rankCommunity Champion
6 years ago

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's avatar
Anonymous
Not applicable
6 years ago

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.