Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
Anonymous
Not applicable

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"
Anonymous
Not applicable

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"
Anonymous
Not applicable

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors