SOLVED

Help with Powerquery/other ways to Split filter data into specific columns

%3CLINGO-SUB%20id%3D%22lingo-sub-3029582%22%20slang%3D%22en-US%22%3EHelp%20with%20Powerquery%2Fother%20ways%20to%20Split%20filter%20data%20into%20specific%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3029582%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3EHi%20All%2C%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3EHope%20you%20are%20doing%20well!..I%20am%20trying%20to%20split%20the%20data%20in%20the%20column%20filter%20into%20specific%20columns%20of%20data...The%20input%20data%20is%20at%20a%20wtype_id%2Cname%20and%20status%20level...%20Please%20find%20attached%20the%20input%20data%20tab%20and%20the%20output%20data%20tab...%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3EWould%20be%20really%20glad%20if%20you%20can%20help%20me...%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3EThanks%2C%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3EArun%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3029582%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3030233%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20Powerquery%2Fother%20ways%20to%20Split%20filter%20data%20into%20specific%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3030233%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F216929%22%20target%3D%22_blank%22%3E%40Arun%20Chandramouli%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20with%20Power%20Query%20that%20could%20be%20like%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-powerquery%22%3E%3CCODE%3Elet%0A%20%20%20%20Source%20%3D%20Excel.CurrentWorkbook()%7B%5BName%3D%22Table1%22%5D%7D%5BContent%5D%2C%0A%0A%20%20%20%20%2F%2F%20%0A%20%20%20%20%23%22Replaced%20Value%22%20%3D%20Table.ReplaceValue(Source%2C%22%3B%22%2C%22%22%2CReplacer.ReplaceText%2C%7B%22filter%22%7D)%2C%0A%20%20%20%20%23%22Replaced%20Value1%22%20%3D%20Table.ReplaceValue(%23%22Replaced%20Value%22%2C%22)%20AND%22%2C%22%2C%20%22%2CReplacer.ReplaceText%2C%7B%22filter%22%7D)%2C%0A%20%20%20%20%23%22Replaced%20Value2%22%20%3D%20Table.ReplaceValue(%23%22Replaced%20Value1%22%2C%22%20%3D%22%2C%22%3A%22%2CReplacer.ReplaceText%2C%7B%22filter%22%7D)%2C%0A%20%20%20%20%23%22Replaced%20Value3%22%20%3D%20Table.ReplaceValue(%23%22Replaced%20Value2%22%2C%22(%22%2C%22%22%2CReplacer.ReplaceText%2C%7B%22filter%22%7D)%2C%0A%20%20%20%20%23%22Replaced%20Value4%22%20%3D%20Table.ReplaceValue(%23%22Replaced%20Value3%22%2C%22)%22%2C%22%22%2CReplacer.ReplaceText%2C%7B%22filter%22%7D)%2C%0A%20%20%20%20%23%22Replaced%20Value5%22%20%3D%20Table.ReplaceValue(%23%22Replaced%20Value4%22%2C%22%20IN%20LIST%20%22%2C%22%3A%20%5B%22%22%22%2CReplacer.ReplaceText%2C%7B%22filter%22%7D)%2C%0A%0A%20%20%20%20%2F%2F%20list%20to%20transform%0A%20%20%20%20%23%22Added%20Custom%22%20%3D%20Table.AddColumn(%0A%20%20%20%20%20%20%20%20%23%22Replaced%20Value5%22%2C%0A%20%20%20%20%20%20%20%20%22Custom%22%2C%0A%20%20%20%20%20%20%20%20each%20Text.Split(%5Bfilter%5D%2C%20%22%2C%20%22%20))%2C%0A%0A%20%20%20%20%23%22Added%20Custom1%22%20%3D%20Table.AddColumn(%0A%20%20%20%20%20%20%20%20%23%22Added%20Custom%22%2C%0A%20%20%20%20%20%20%20%20%22Custom.1%22%2C%0A%20%20%20%20%20%20%20%20each%20List.Transform(%0A%20%20%20%20%20%20%20%20%20%20%20%20%5BCustom%5D%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20each%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20if%20Text.Contains(%20_%2C%20%22%5B%22%20)%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20then%20_%20%26amp%3B%20Character.FromNumber(34)%20%26amp%3B%20%20%22%5D%22%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20else%20_))%2C%0A%20%20%20%20%23%22Added%20Custom2%22%20%3D%20Table.AddColumn(%0A%20%20%20%20%20%20%20%20%23%22Added%20Custom1%22%2C%0A%20%20%20%20%20%20%20%20%22Custom.2%22%2C%0A%20%20%20%20%20%20%20%20each%20List.Transform(%0A%20%20%20%20%20%20%20%20%20%20%20%20%5BCustom.1%5D%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20each%20Text.Replace(%20_%2C%20%22%2C%22%2C%20%22%22%22%2C%22%22%22)%20))%2C%0A%0A%20%20%20%20%2F%2F%20combine%20back%20to%20json%0A%20%20%20%20%23%22Added%20Custom3%22%20%3D%20Table.AddColumn(%0A%20%20%20%20%20%20%20%20%23%22Added%20Custom2%22%2C%0A%20%20%20%20%20%20%20%20%22Custom.3%22%2C%0A%20%20%20%20%20%20%20%20each%20%22%7B%22%20%26amp%3B%20Text.Combine(%5BCustom.2%5D%2C%20%22%2C%20%22)%20%26amp%3B%20%22%7D%22)%2C%0A%20%20%20%20%0A%20%20%20%20%23%22Parsed%20JSON%22%20%3D%20Table.TransformColumns(%23%22Added%20Custom3%22%2C%7B%7B%22Custom.3%22%2C%20Json.Document%7D%7D)%2C%0A%20%20%20%20%23%22Expanded%20Custom.3%22%20%3D%20Table.ExpandRecordColumn(%0A%20%20%20%20%20%20%20%20%23%22Parsed%20JSON%22%2C%0A%20%20%20%20%20%20%20%20%22Custom.3%22%2C%0A%20%20%20%20%20%20%20%20%7B%22orgId%22%2C%20%22careType%22%2C%20%22contextId%22%2C%20%22specialtyId%22%7D%2C%0A%20%20%20%20%20%20%20%20%7B%22orgId%22%2C%20%22careType%22%2C%20%22contextId%22%2C%20%22specialtyId%22%7D)%2C%0A%20%20%20%20%23%22Expanded%20contextId%22%20%3D%20Table.ExpandListColumn(%0A%20%20%20%20%20%20%20%20%23%22Expanded%20Custom.3%22%2C%20%22contextId%22)%2C%0A%20%20%20%20%23%22Expanded%20specialtyId%22%20%3D%20Table.ExpandListColumn(%0A%20%20%20%20%20%20%20%20%23%22Expanded%20contextId%22%2C%20%22specialtyId%22)%2C%0A%20%20%20%20%23%22Removed%20Other%20Columns%22%20%3D%20Table.SelectColumns(%0A%20%20%20%20%20%20%20%20%23%22Expanded%20specialtyId%22%2C%0A%20%20%20%20%20%20%20%20%7B%22wtype_id%22%2C%20%22name%22%2C%20%22status%22%20%2C%20%22orgId%22%2C%20%22careType%22%2C%20%22specialtyId%22%2C%20%22contextId%22%7D)%2C%0A%0A%20%20%20%20%23%22Changed%20Type%22%20%3D%20Table.TransformColumnTypes(%0A%20%20%20%20%20%20%20%20%23%22Removed%20Other%20Columns%22%2C%0A%20%20%20%20%20%20%20%20%7B%0A%20%20%20%20%20%20%20%20%20%20%20%20%7B%22specialtyId%22%2C%20Int64.Type%7D%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%7B%22contextId%22%2C%20Int64.Type%7D%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%7B%22status%22%2C%20Int64.Type%7D%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%7B%22wtype_id%22%2C%20Int64.Type%7D%0A%20%20%20%20%20%20%20%20%7D)%0Ain%0A%20%20%20%20%23%22Changed%20Type%22%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Ethe%20idea%20is%20to%20convert%20texts%20to%20json%20format%20and%20when%20parse%20by%20standard%20function.%20result%20is%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20533px%3B%22%3E%3Cspan%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20533px%3B%22%3E%3Cimg%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F331534iD6AEC967D978F1A0%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2Fspan%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Contributor

Hi All,

Hope you are doing well!..I am trying to split the data in the column filter into specific columns of data...The input data is at a wtype_id,name and status level... Please find attached the input data tab and the output data tab...

Would be really glad if you can help me...





Thanks,
Arun

3 Replies
best response confirmed by Arun Chandramouli (Contributor)
Solution

@Arun Chandramouli 

If with Power Query that could be like

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

    // 
    #"Replaced Value" = Table.ReplaceValue(Source,";","",Replacer.ReplaceText,{"filter"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",") AND",", ",Replacer.ReplaceText,{"filter"}),
    #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1"," =",":",Replacer.ReplaceText,{"filter"}),
    #"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2","(","",Replacer.ReplaceText,{"filter"}),
    #"Replaced Value4" = Table.ReplaceValue(#"Replaced Value3",")","",Replacer.ReplaceText,{"filter"}),
    #"Replaced Value5" = Table.ReplaceValue(#"Replaced Value4"," IN LIST ",": [""",Replacer.ReplaceText,{"filter"}),

    // list to transform
    #"Added Custom" = Table.AddColumn(
        #"Replaced Value5",
        "Custom",
        each Text.Split([filter], ", " )),

    #"Added Custom1" = Table.AddColumn(
        #"Added Custom",
        "Custom.1",
        each List.Transform(
            [Custom],
            each
                if Text.Contains( _, "[" )
                then _ & Character.FromNumber(34) &  "]"
                else _)),
    #"Added Custom2" = Table.AddColumn(
        #"Added Custom1",
        "Custom.2",
        each List.Transform(
            [Custom.1],
            each Text.Replace( _, ",", """,""") )),

    // combine back to json
    #"Added Custom3" = Table.AddColumn(
        #"Added Custom2",
        "Custom.3",
        each "{" & Text.Combine([Custom.2], ", ") & "}"),
    
    #"Parsed JSON" = Table.TransformColumns(#"Added Custom3",{{"Custom.3", Json.Document}}),
    #"Expanded Custom.3" = Table.ExpandRecordColumn(
        #"Parsed JSON",
        "Custom.3",
        {"orgId", "careType", "contextId", "specialtyId"},
        {"orgId", "careType", "contextId", "specialtyId"}),
    #"Expanded contextId" = Table.ExpandListColumn(
        #"Expanded Custom.3", "contextId"),
    #"Expanded specialtyId" = Table.ExpandListColumn(
        #"Expanded contextId", "specialtyId"),
    #"Removed Other Columns" = Table.SelectColumns(
        #"Expanded specialtyId",
        {"wtype_id", "name", "status" , "orgId", "careType", "specialtyId", "contextId"}),

    #"Changed Type" = Table.TransformColumnTypes(
        #"Removed Other Columns",
        {
            {"specialtyId", Int64.Type},
            {"contextId", Int64.Type},
            {"status", Int64.Type},
            {"wtype_id", Int64.Type}
        })
in
    #"Changed Type"

the idea is to convert texts to json format and when parse by standard function. result is

image.png

Thank you so much @Sergei Baklan..Really appreciate your help!