Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Explose Lines in Power Query

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

Interesting Links
Need Help
Tips and Tricks
  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    4 years ago

    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"

     

3 Replies

  • Syndicate_Admin's avatar
    Syndicate_Admin
    Administrator
    4 years ago

    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's avatar
      Anonymous
      Not applicable
      4 years ago

      Hello ,

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

      I need to replae the code?

      sorry it's not clear

  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    Most Valuable Professional
    4 years ago

    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"