Forum Discussion

Gameboy334's avatar
Gameboy334
Regular Visitor
3 years ago
Solved

Help with modifying data

I have a dataset coming in that has orders as a single line, with multiple columns showing the items and the details of the sales. I am trying to get the data transformed to an easier to read format....
Need Help
  • wdx223_Daniel's avatar
    wdx223_Daniel
    2 years ago

    = #table(List.FirstN(Table.ColumnNames(KeepColumns),11) & {"Discount Amount","Discount Total","Item SubTotal","Item Total","Price","Product Name","Quantity","SKU"} , List.TransformMany(Table.ToRows(KeepColumns) , each List.Zip({{1},List.Split(List.Skip(_,11),8)}) , (x,y)=>List.FirstN(x,2)&List.ReplaceRange(y{1},0,0,if y{0}=null then List.Repeat({null},9) else List.Range(x,2,9))))

wdx223_Daniel's avatar
wdx223_Daniel
Icon for Community Champion rankCommunity Champion
3 years ago

NewStep== #table(List.FirstN(Table.ColumnNames(YourTableName),6)&{"Item","QTY","Price"},List.TransformMany(Table.ToRows(YourTableName),each List.Zip({{1},List.Split(List.Skip(_,6),3)}),(x,y)=>List.FirstN(x,2)&List.ReplaceRange(y{1},0,0,if y{0}=null then List.Repeat({null},4) else List.Range(x,2,4))))

  • Gameboy334's avatar
    Gameboy334
    Regular Visitor
    3 years ago

    Thank you for the reply. Can you help me understand the components of the formula that you gave me? The full dataset is much more data than the example that I gave and I will need to customize the formula to fit my needs.

  • Gameboy334's avatar
    Gameboy334
    Regular Visitor
    2 years ago

    I have entered the below as a new step but am getting the error below. Any thoughts on what I did wrong?

     

    = KeepColumns(List.FirstN(Table.ColumnNames(KeepColumns),11) & {"Discount Amount","Discount Total","Item SubTotal","Item Total","Price","Product Name","Quantity","SKU"} , List.TransformMany(Table.ToRows(KeepColumns) , each List.Zip({{1},List.Split(List.Skip(_,11),8)}) , (x,y)=>List.FirstN(x,2)&List.ReplaceRange(y{1},0,0,if y{0}=null then List.Repeat({null},4) else List.Range(x,2,4))))

     

    "Expression.Error: We cannot convert a value of type Table to type Function.
    Details:
    Value=[Table]
    Type=[Type]