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
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))))