Forum Discussion
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. Below is an example of how the data is currently showing up.
| OrderID | Date | Tax | Shipping | Tip | Total | Item1 | Qty1 | ItemPrice1 | Item2 | Qty2 | ItemPrice2 |
| 12345 | 9/3/23 | 10 | 15 | 10 | 95 | 951 | 2 | 24 | 856 | 1 | 12 |
Is there a way to change this to the below?
| OrderID | Date | Tax | Shipping | Tip | Total | Item | Qty | ItemPrice |
| 12345 | 9/3/23 | 10 | 15 | 10 | 95 | 951 | 2 | 24 |
| 12345 | 9/3/23 | 856 | 1 | 12 |
= #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))))
4 Replies
- wdx223_Daniel
Community Champion
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))))
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.
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]
- wdx223_Daniel
= #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))))
