SOLVED

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

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!