Explose Lines in Power Query

[Anonymous]

Hello All,

I need your help for power query issue, il need to explose lines in my table regarding the category and Product Columns like shown below :

Original Table :

Needed result :

So there is any option to do this please in Power Query?

Thank you in advance for your Help

[Vijay_A_Verma · Accepted solution]

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQpJLS4BUo4gbKgUqxOtZAQVLYHKWDuB5awdjcDSxlBpQyNjiKy1M5B2hkiaQCVNQHIuIAwRN4WKm5pZwiWsIyD2maEY6GwNknU2VoqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Client = _t, Category = _t, Product = _t]),
    Custom1 = Table.ReplaceValue(Source,each [Category],each Text.Split([Category],";"),Replacer.ReplaceValue,{"Category"}),
    Custom2 = Table.ReplaceValue(Custom1,each [Product],each Text.Split([Product],";"),Replacer.ReplaceValue,{"Product"}),
    #"Expanded Category" = Table.ExpandListColumn(Custom2, "Category"),
    #"Expanded Product" = Table.ExpandListColumn(#"Expanded Category", "Product")
in
    #"Expanded Product"

[Syndicate_Admin]

Hi @Syndicate_Admin ,

I hope my sample code will help:

ClearCollect(
collsampledata,
{
ID: 1,
Client: "Test",
Category: "A"
},
{
ID: 2,
Client: "Test2",
Category: "A;B"
}
);
ForAll(
collsampledata As currentrow,

ForAll( Split(
currentrow.Category,
";"
) As newdata,Collect(colldata,{ID:currentrow.ID, Client:currentrow.Client,Category:newdata.Result}))
)

[Anonymous]

Hello ,

How I need to do? I need to create a Function ?

I need to replae the code?

sorry it's not clear