Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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,
Solved! Go to Solution.
Hi @Anonymous ,
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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"
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))),
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"
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
result
Hi @Anonymous ,
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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!
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!!!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.