Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

MATRIX

Hi, I'm trying to use a MATRIX for my dataset. My input data source has data in the following format:   Date H_Staff A_Staff T_Staff St_Count 30/05/20 1 3 2 34 30/06/20 3 7 3 ...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    6 years ago

    Anonymous - Try unpivoting your last 4 columns in Power Query Editor and this this should be very easy.

  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi Anonymous 

    Just like Greg_Deckler and mahoneypat  ‘s reply said Unpivot in Power Query Editor is a good way to achieve your goal.

    The way to rename the column header is easy, you can right click the column headers in Power Query Editor or in Fields and choose Rename.

    And you may try my way.

    I build a table like yours to have a test.

    Use raw data to build a matrix.

    Then you select Show on rows in Values.

    Result:

    The result by this way may don’t have column headers, but you can use the original data model to achieve your goal.

    And you can build a text box and use group by right-click after selecting both two visuals.

    You can download the pbix file from this link: MATRIX

     

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

     

    Best Regards,

    Rico Zhou

     

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

Anonymous - Try unpivoting your last 4 columns in Power Query Editor and this this should be very easy.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago
    Thanks Greg. Where do I then rename rows? Do I rename using DAX if statements?
    • mahoneypat's avatar
      mahoneypat
      Icon for Microsoft Employee rankMicrosoft Employee
      6 years ago

      Here is some example M code to show you how to unpivot your data to set it up for simple analysis.  To see how it works, just create a blank query, go to Advanced Editor, and replace the text there with the M code below.

       

      let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjbQNzDVNzJQ0lEyBGJjIDYC0SZKsToQWTOILEjGHEqbGEFkDfUNzCGyID0mUGxqoRQbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, H_Staff = _t, A_Staff = _t, T_Staff = _t, St_Count = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type text}, {"H_Staff", Int64.Type}, {"A_Staff", Int64.Type}, {"T_Staff", Int64.Type}, {"St_Count", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Date"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "Staff"}, {"Value", "Count"}})
in
    #"Renamed Columns"

       

      Once you load that table, you just need to make a simple Table visual with the Staff column and the Sum of the Count column to get your desired result.

      If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

      Regards,

      Pat