cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

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:

 

EXAMPLE.PNG

 

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,

1 ACCEPTED SOLUTION
ImkeF
Super User
Super User

Hi @Anonymous ,

  1. Check the column "Row" and unpivot other columns
  2. Check the resulting "Attribute"-column and extract the first 3 characters from it
  3. Pivot that new column with "Values" in the values-section

 

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

View solution in original post

7 REPLIES 7
Rocco_sprmnt21
Community Champion
Community Champion

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"
Rocco_sprmnt21
Community Champion
Community Champion

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"
Rocco_sprmnt21
Community Champion
Community Champion

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"
ziying35
Impactful Individual
Impactful Individual

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
ImkeF
Super User
Super User

Hi @Anonymous ,

  1. Check the column "Row" and unpivot other columns
  2. Check the resulting "Attribute"-column and extract the first 3 characters from it
  3. Pivot that new column with "Values" in the values-section

 

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

Anonymous
Not applicable

@ImkeF 

 

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!

Anonymous
Not applicable

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:

 

Capture.PNG

 

Capture.PNG

 

Murillmi_0-1613839191179.png

 

Thank you so much for your help!!!

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors