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

[Arun Chandramouli]

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

[#2 · Accepted 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

[#3]

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

[#4]

@Arun Chandramouli , you are welcome