Forum Discussion

afaherty's avatar
afaherty
Icon for Helper V rankHelper V
1 year ago
Solved

Issue with pivoting via DAX

Hello, This is piggy-backing off of a previous post I had made and I continue to struggle which is my fault because my real data has more columns of interest that I did not share (apologies for that...
pivot
  • AntrikshSharma's avatar
    AntrikshSharma
    1 year ago

    afaherty This can be easily done with Power Query no need to use DAX for data transformation.

     

    let
    Source = RawData,
    Unpivot = Table.UnpivotOtherColumns ( Source, { "StudentID", "Level", "Test" }, "A", "V" ),
    MergeCols = Table.CombineColumns (
        Unpivot,
        { "Test", "A" },
        Combiner.CombineTextByDelimiter ( "_", QuoteStyle.None ),
        "Merged"
    ),
    Pivot = Table.Pivot ( MergeCols, List.Distinct ( MergeCols[Merged] ), "Merged", "V", ( x ) => x{0}? )
in
    Pivot

     

     

AntrikshSharma's avatar
AntrikshSharma
Icon for Community Champion rankCommunity Champion
1 year ago

afaherty This can be easily done with Power Query no need to use DAX for data transformation.

 

let
    Source = RawData,
    Unpivot = Table.UnpivotOtherColumns ( Source, { "StudentID", "Level", "Test" }, "A", "V" ),
    MergeCols = Table.CombineColumns (
        Unpivot,
        { "Test", "A" },
        Combiner.CombineTextByDelimiter ( "_", QuoteStyle.None ),
        "Merged"
    ),
    Pivot = Table.Pivot ( MergeCols, List.Distinct ( MergeCols[Merged] ), "Merged", "V", ( x ) => x{0}? )
in
    Pivot