Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Extract column information from rows within a table

Hi,

I would like to extract the individual rows which are from 1-11 (and they keep on repeating) into 11 columns with their respective values.

 

 

Thanks,

Modasher.

Need Help
Show and Tell
  • v-gizhi-msft's avatar
    v-gizhi-msft
    6 years ago

    Hi,

     

    Please try to add an index column first.

    Then try to create this calculated column:

    Group = ROUNDUP('Table'[Index]/11,0)

    Choose matrix visual, it shows:

    Here is my test pbix file:

    pbix 

    Hope this helps.

     

    Best Regards,

    Giotto

3 Replies

  • v-gizhi-msft's avatar
    v-gizhi-msft
    Community Support
    6 years ago

    Hi,

     

    Please try to add an index column first.

    Then try to create this calculated column:

    Group = ROUNDUP('Table'[Index]/11,0)

    Choose matrix visual, it shows:

    Here is my test pbix file:

    pbix 

    Hope this helps.

     

    Best Regards,

    Giotto

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Anonymous 

     

    Do you mean you want to pivot the every 11 rows to a table with 11 columns? Use Power Query to pivot it.

     

    #1 add a custom column - Table.AddColumn(your last step, "Custom", each Number.IntegerDivide([Column Index]-1,11))

    #2 remove the index column - Table.SelectColumns(#"Added Custom",{"Custom", "display_name","value"})

    #3 pivot the table - Table.Pivot(#"Removed Other Columns", List.Distinct(#"Removed Other Columns"[display_name]), "display_name", "value")