Forum Discussion

Hussain_R's avatar
Hussain_R
Frequent Visitor
2 years ago
Solved

[Help] How to convert a list values to row values

Hello, I have a query that comes from our ERP putting out recievables report showing the bill reference, bill date, bill value and the customer name. However, the report comes out as below: Column 1 Column 2 BillF 01/01/2024 BillF HDM24001 BillF ABC Corp BillV 234.70 I want to change this structure to show this information as rows, however I'm unable to pivot, because the first 3 values in column1 isn't different, so it throws an error. This list continues to another 6200 rows where the information for each bill is presented vertically in columns and I want them in rows. Any idea how do I go about correcting the orientation?
Need Help
  • amitchandak's avatar
    amitchandak
    2 years ago

    Hussain_R , Please paste the data in table format. Not able to understand the same

    Based on what I got

    Assume the current format is

    Column 1 Column 2
    BillF 1/1/2024
    BillF HDM24001
    BillF ABC Corp
    BillV 234.7
    ... ...

     

    Expected is

    Bill Date Bill Reference Customer Name Bill Value
    1/1/2024 HDM24001 ABC Corp 234.7
    ... ... ...

    ...

     

     

    Add an index column from 0

     

    Add another column, divide by 4, Group Index

    = Table.AddColumn(#"Previous Step", "Group Index", each Number.IntegerDivide([Index], 4))

     

    Pivot the Table with the do not Aggregate option  in Advance and try

     

     

    If this does not help
    Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

1 Reply

  • amitchandak's avatar
    amitchandak
    Super User
    2 years ago

    Hussain_R , Please paste the data in table format. Not able to understand the same

    Based on what I got

    Assume the current format is

    Column 1 Column 2
    BillF 1/1/2024
    BillF HDM24001
    BillF ABC Corp
    BillV 234.7
    ... ...

     

    Expected is

    Bill Date Bill Reference Customer Name Bill Value
    1/1/2024 HDM24001 ABC Corp 234.7
    ... ... ...

    ...

     

     

    Add an index column from 0

     

    Add another column, divide by 4, Group Index

    = Table.AddColumn(#"Previous Step", "Group Index", each Number.IntegerDivide([Index], 4))

     

    Pivot the Table with the do not Aggregate option  in Advance and try

     

     

    If this does not help
    Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.