cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Murillmi
Helper I
Helper I

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

Hi @Murillmi ,

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

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

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

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, @Murillmi 

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

Hi @Murillmi ,

  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

@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!

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
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors