Forum Discussion
Spliting rows with spicific columns
Hello Everyone and thank you in advance for your help,
I'm struggling with this problem, here's an example:
I manage to split the rows using M but I can't find a way to make the split while keeping the QTY attached to each SKU. I would really appreaciate any help!
Thank you for your time,
Hi Anonymous ,
- Check the column "Row" and unpivot other columns
- Check the resulting "Attribute"-column and extract the first 3 characters from it
- Pivot that new column with "Values" in the values-section
7 Replies
Hi Anonymous ,
- Check the column "Row" and unpivot other columns
- Check the resulting "Attribute"-column and extract the first 3 characters from it
- Pivot that new column with "Values" in the values-section
Hey there!
ImkeFthis looks like a great and easy way to approach this problem but I might be doing something wrong, could you please advice?Until this step it seems like the data is ready for being pivoted but it shows the following error:
Thank you so much for your help!!!
I managed to make it work! I was extracting just the first 3 letters of the column instead of splitting it!
Thank you so much! This will save me a ton of time!
Hi, Anonymous
Try this:
let Source = Table.FromRecords(Json.Document(Binary.Decompress(Binary.FromText("lZC9CsJAEAbf5atT3F9ArlNjTEyh0RxyiG8QtBIL8d3V4/aygoXXzS4sM+zpgf31DisLHDonYTFfLBEGBXu5jWNgzdgwLon7wb+PdQDFlpqxYRwPn0X0K+avkh9VHWP+DJAlFagsvU76/ria9IN3WXojSC+FyAowKcC7dgrYbdsUgE2zBhWga2r8/ICihFkqiGQ+WV/28ws=", BinaryEncoding.Base64),Compression.Deflate))), trans = Table.ToList(Source, each List.Transform(List.RemoveItems(List.Zip(List.Split(List.Skip(_), 5)), {{null, null}}), (lst)=>{_{0}}&lst)), result = Table.FromRows(List.Combine(trans), {"Row", "SKU", "QTY"}) in resultlet Source = Table.FromRecords(Json.Document(Binary.Decompress(Binary.FromText("lZC9CsJAEAbf5atT3F9ArlNjTEyh0RxyiG8QtBIL8d3V4/aygoXXzS4sM+zpgf31DisLHDonYTFfLBEGBXu5jWNgzdgwLon7wb+PdQDFlpqxYRwPn0X0K+avkh9VHWP+DJAlFagsvU76/ria9IN3WXojSC+FyAowKcC7dgrYbdsUgE2zBhWga2r8/ICihFkqiGQ+WV/28ws=", BinaryEncoding.Base64),Compression.Deflate))), ttc=Table.ToColumns(Source), c1=List.Repeat(ttc{0},5), sku=List.Combine(List.Range(ttc,1,5)), qty=List.Combine(List.Range(ttc,6,5)), tfc=Table.FromColumns({c1,sku,qty},{"idx","sku","qty"}), #"Ordinate righe" = Table.Sort(tfc,{{"idx", Order.Ascending}}), #"Filtrate righe" = Table.SelectRows(#"Ordinate righe", each ([sku] <> null)) in #"Filtrate righe"let Source = Table.FromRecords(Json.Document(Binary.Decompress(Binary.FromText("lZC9CsJAEAbf5atT3F9ArlNjTEyh0RxyiG8QtBIL8d3V4/aygoXXzS4sM+zpgf31DisLHDonYTFfLBEGBXu5jWNgzdgwLon7wb+PdQDFlpqxYRwPn0X0K+avkh9VHWP+DJAlFagsvU76/ria9IN3WXojSC+FyAowKcC7dgrYbdsUgE2zBhWga2r8/ICihFkqiGQ+WV/28ws=", BinaryEncoding.Base64),Compression.Deflate))), cols=Table.ColumnNames(Source), rows=Table.RowCount(Source), ncols=(Table.ColumnCount(Source)-1)/2, lstrecs=List.Transform({0..rows-1}, (r)=> List.Transform({1..ncols}, each [Row=r+1,SKU=Table.Column(Source, cols{_}){r},QTY=Table.Column(Source, cols{ncols+_}){r}])), recs=List.Combine(lstrecs), tfr=Table.FromRecords(recs), #"Filtrate righe" = Table.SelectRows(tfr, each ([QTY] <> null)) in #"Filtrate righe"let Source = Table.FromRecords(Json.Document(Binary.Decompress(Binary.FromText("lZC9CsJAEAbf5atT3F9ArlNjTEyh0RxyiG8QtBIL8d3V4/aygoXXzS4sM+zpgf31DisLHDonYTFfLBEGBXu5jWNgzdgwLon7wb+PdQDFlpqxYRwPn0X0K+avkh9VHWP+DJAlFagsvU76/ria9IN3WXojSC+FyAowKcC7dgrYbdsUgE2zBhWga2r8/ICihFkqiGQ+WV/28ws=", BinaryEncoding.Base64),Compression.Deflate))), rows=Table.RowCount(Source), ncols=(Table.ColumnCount(Source)-1)/2, lstrecs=List.Transform({0..rows-1}, (r)=> List.Transform({1..ncols}, each [Row=r+1,SKU=Table.Column(Source, "SKU"&Text.From(_)){r},QTY=Table.Column(Source, "QTY"&Text.From(_)){r}])), recs=List.Combine(lstrecs), tfr=Table.FromRecords(recs), #"Filtrate righe" = Table.SelectRows(tfr, each ([QTY] <> null)) in #"Filtrate righe"
