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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
HaleCarGuy
New Member

How can I separate multiple products purchased from a single field then slice by those products?

My dataset contains a column where all products purchased in a single transaction are combined in a single field. These products are separated by a distinct character (the character is "|") so I do not have a problem delimiting them, however, they are not in any particular order, and each customer could potentially purchase any combination of products. 

 

My first guess was to separate them into separate columns. When I separate them into new columns it gives me columns"Product.1" "Product.2" etc. but if I then try to sort by one of the columns it will not identify later instances of the same value in other columns:

 

NameProductProduct.1Product.2Product.3

Product.4

Val Kilmera | b | c | dabcd
Simon Bolivarc | a | bcabnull
C.S. Lewisb | d |bdnullnull

 

(In the above example, the "A" product in column "Product.1" column is not associated with the "a" in "Product.2")

 

I am sure there is another approach to this, I am just not seeing it. 

 

This is part of a template where multiple datasets will be imported over time. All of the datasets will have the same Columns but not necessarily the same products, so if at all possible a solution that is based on some kind of logic versus looking up specific values would be ideal. I know this may be a big ask, but I am not able to control how the data is coming to me and any help would be greatly appreciated. 

1 ACCEPTED SOLUTION
dufoq3
Super User
Super User

Hi @HaleCarGuy, try this. It should be dynamic.

 

Dynamic split without product sort result:

dufoq3_0-1709983287683.png

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkvMUfDOzMlNLVLSUUpUqFFIAuJkIE4B8lUMDZRidaKVgjNz8/MUnPJzMssSQepA8mC1IDVGEDXOesF6Cj6p5ZnFQEGQISkKNSBpY6B0LAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Product = _t, #"Purchase Price" = _t]),
    Ad_NoOfComns = Table.AddColumn(Source, "No of Columns", each List.Count(Text.Split([Product], "|")), Int64.Type),
    Columns = [ a = List.Max(Ad_NoOfComns[No of Columns]),
    b = List.Transform({1..a}, (x)=> "Product" & Text.From(x)) 
  ][b],
    StepBack = Source,
    SplitColumnDynamic = Table.SplitColumn(StepBack, "Product", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), Columns),
    TextTrimDynamic = Table.TransformColumns(SplitColumnDynamic, List.Transform(Columns, (colName)=> { colName, each if Text.Trim(_) = "" then null else Text.Trim(_), type text } ) )
in
    TextTrimDynamic

 

 

Dynamic split with product sort result

dufoq3_0-1709984194890.png

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkvMUfDOzMlNLVLSUUpUqFFIAuJkIE4B8lUMDZRidaKVgjNz8/MUnPJzMssSQepA8mC1IDVGEDXOesF6Cj6p5ZnFQEGQISkKNSBpY6B0LAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Product = _t, #"Purchase Price" = _t]),
    Ad_NoOfComns = Table.AddColumn(Source, "No of Columns", each List.Count(Text.Split([Product], "|")), Int64.Type),
    Ad_Products = Table.AddColumn(Ad_NoOfComns, "Products", each List.Select(List.Transform(Text.Split([Product], "|"), Text.Trim), (x)=> x <> "") , type list),
    Products = List.Buffer(List.Distinct(List.Combine(Ad_Products[Products]))),
    StepBack = Ad_Products,
    Ad_ProductColumns = List.Accumulate( 
       List.Zip({{1..List.Count(Products)}, Products}),
       StepBack,
       (s,c)=> Table.AddColumn(s, "Product" & Text.From(c{0}), each if List.Contains([Products], c{1}) then c{1} else null, type text)     
),
    RemovedColumns = Table.RemoveColumns(Ad_ProductColumns,{"No of Columns", "Products"})
in
    RemovedColumns

 


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

View solution in original post

3 REPLIES 3
dufoq3
Super User
Super User

Hi @HaleCarGuy, try this. It should be dynamic.

 

Dynamic split without product sort result:

dufoq3_0-1709983287683.png

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkvMUfDOzMlNLVLSUUpUqFFIAuJkIE4B8lUMDZRidaKVgjNz8/MUnPJzMssSQepA8mC1IDVGEDXOesF6Cj6p5ZnFQEGQISkKNSBpY6B0LAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Product = _t, #"Purchase Price" = _t]),
    Ad_NoOfComns = Table.AddColumn(Source, "No of Columns", each List.Count(Text.Split([Product], "|")), Int64.Type),
    Columns = [ a = List.Max(Ad_NoOfComns[No of Columns]),
    b = List.Transform({1..a}, (x)=> "Product" & Text.From(x)) 
  ][b],
    StepBack = Source,
    SplitColumnDynamic = Table.SplitColumn(StepBack, "Product", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), Columns),
    TextTrimDynamic = Table.TransformColumns(SplitColumnDynamic, List.Transform(Columns, (colName)=> { colName, each if Text.Trim(_) = "" then null else Text.Trim(_), type text } ) )
in
    TextTrimDynamic

 

 

Dynamic split with product sort result

dufoq3_0-1709984194890.png

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkvMUfDOzMlNLVLSUUpUqFFIAuJkIE4B8lUMDZRidaKVgjNz8/MUnPJzMssSQepA8mC1IDVGEDXOesF6Cj6p5ZnFQEGQISkKNSBpY6B0LAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Product = _t, #"Purchase Price" = _t]),
    Ad_NoOfComns = Table.AddColumn(Source, "No of Columns", each List.Count(Text.Split([Product], "|")), Int64.Type),
    Ad_Products = Table.AddColumn(Ad_NoOfComns, "Products", each List.Select(List.Transform(Text.Split([Product], "|"), Text.Trim), (x)=> x <> "") , type list),
    Products = List.Buffer(List.Distinct(List.Combine(Ad_Products[Products]))),
    StepBack = Ad_Products,
    Ad_ProductColumns = List.Accumulate( 
       List.Zip({{1..List.Count(Products)}, Products}),
       StepBack,
       (s,c)=> Table.AddColumn(s, "Product" & Text.From(c{0}), each if List.Contains([Products], c{1}) then c{1} else null, type text)     
),
    RemovedColumns = Table.RemoveColumns(Ad_ProductColumns,{"No of Columns", "Products"})
in
    RemovedColumns

 


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

HaleCarGuy
New Member

Thanks for the answer! This might be making progress, but when I do that, it duplicates all of the data in the row. Now as an example, the price is entered in three times throwing off all of the other calculations in my template. 

 

This is a very simplified example below as the data has 30+ columns but it demonstrates the issue:

 

NameProductPurchase Price
Val Kilmera | b | c | d$540

 

Becomes:

NameProductPurchase Price
Val Kilmera$540
Val Kilmer   b$540
Val Kilmer   c$540
Val Kilmer   d$540

 

Sorry I should have mentioned there was many other columns before. 

lbendlin
Super User
Super User

Use the other option of the splitter - split into rows.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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

Top Solution Authors