Forum Discussion

fernandoC's avatar
fernandoC
Helper V
5 years ago

Splitting Column per Category

Hi!   I'm trying to split a column so that it represents one category per column. I have 2 columns: ID & Custom Field name where for example there multiple "Years of Experience" per the same ID and...
General Comment
Need Help
Tips and Tricks
fernandoC's avatar
fernandoC
Helper V
5 years ago

Hi Greg! Greg_Deckler 

 

Please find below a text test table:

 

IDCustom Field NameCustom Field Value
10100Contact Date15/5/2020
10100Contact Date15/5/2020
10100Contact Date15/5/2020
10100Years of Exp.1-3
10100Years of Exp.1-3
10100Years of Exp.1-3
10200Contact Date20/3/2020

10200

Contact Date20/3/2020
10200Contact Date20/3/2020
10200Years of Exp.2-5
10200Years of Exp.2-5
10200Years of Exp.2-5

 

 

The ideal would be to have the following:

IDCustom Field 1Custom Field ValueCustom Field 2Custom Field Value
10100Contact Date15/5/2020Years of Experience1-3
10200Contact Date20/3/2020Years of Experience2-5
Greg_Deckler's avatar
Greg_Deckler
Community Champion
5 years ago

fernandoC So a matrix visualization like this? PBIX is attached, Page 8. This uses you source data as posted, no transformation.

 

  • fernandoC's avatar
    fernandoC
    Helper V
    5 years ago

    Hi Greg! Greg_Deckler 

     

    Yes great! that would be the ideal outcome. I would like to have level of order in my raw data as I will be using those columns for multiple visualizations.

     

    Thanks again for your help. 

     

    Best,

    • Greg_Deckler's avatar
      Greg_Deckler
      Community Champion
      5 years ago

      fernandoC So solved? Sorry not clear on level of order.

      • fernandoC's avatar
        fernandoC
        Helper V
        5 years ago

        Hi Greg_Deckler ,

         

        So, It works great using a Matrix Visual, however, when I try to use it for a bar chart it doesn't work since the structure of the data needs to be as displayed on the Matrix Visual. 

         

        So I've created a measure to count Hires that are equal to "Issue Type=Offer" & "Custom Field Value = Accepted", and I want to filter them by Disciplines (which are currently under "Custom Field Name") the issue comes when using the axis "Custom Field Name" as both "Accepted & Disciplines" come from the same column.  Hope it makes sense.