Filtering out columns in M

[EvPeCO]

I'm looking for a solution to filter out columns before it gets into Power Query Editor based on a value in the json result (it's being returned from an API Endpoint but created a simple json result). I've copied the M code below. I couldn't see a way to upload the PBIX file.

What I'm trying to do, is to filter out the columns where "schemaType":"Dynamic" and keep the columns where "schemaType":"Static". The static columns never change whereas the dynamic columns change (the name and field names change between result sets which is causing metadata issues and needs manual updates. The data we are reporting off of is only in the static columns anyways. 

Is there a way to filter out the dynamic columns?

@ImkeF helped me with the key and value parsing. Maybe you have a pointer for me on this?

let
    Source = "[#(cr)#(lf)    {#(cr)#(lf)        ""jobId"":""a"",#(cr)#(lf)        ""columnMetadata"":[#(cr)#(lf)            {#(cr)#(lf)                ""name"":""JOB ID"",#(cr)#(lf)                ""FieldName"":""job_id"",#(cr)#(lf)                ""unitText"":null,#(cr)#(lf)                ""columnIndex"":0,#(cr)#(lf)                ""dataType"":""string"",#(cr)#(lf)                ""schemaType"":""Static""#(cr)#(lf)            },#(cr)#(lf)            {#(cr)#(lf)                ""name"":""TASK ID"",#(cr)#(lf)                ""FieldName"":""TASK_id"",#(cr)#(lf)                ""unitText"":null,#(cr)#(lf)                ""columnIndex"":1,#(cr)#(lf)                ""dataType"":""string"",#(cr)#(lf)                ""schemaType"":""Static""#(cr)#(lf)            }#(cr)#(lf)        ],#(cr)#(lf)        ""rowData"":[#(cr)#(lf)            [#(cr)#(lf)                ""a"",#(cr)#(lf)                ""t1""#(cr)#(lf)            ],#(cr)#(lf)            [#(cr)#(lf)                ""a"",#(cr)#(lf)                ""t2""#(cr)#(lf)            ]#(cr)#(lf)        ]#(cr)#(lf)    },#(cr)#(lf)    {#(cr)#(lf)        ""jobId"":""b"",#(cr)#(lf)        ""columnMetadata"":[#(cr)#(lf)            {#(cr)#(lf)                ""name"":""JOB ID"",#(cr)#(lf)                ""FieldName"":""job_id"",#(cr)#(lf)                ""unitText"":null,#(cr)#(lf)                ""columnIndex"":0,#(cr)#(lf)                ""dataType"":""string"",#(cr)#(lf)                ""schemaType"":""Static""#(cr)#(lf)            },#(cr)#(lf)            {#(cr)#(lf)                ""name"":""TASK ID"",#(cr)#(lf)                ""FieldName"":""TASK_id"",#(cr)#(lf)                ""unitText"":null,#(cr)#(lf)                ""columnIndex"":1,#(cr)#(lf)                ""dataType"":""string"",#(cr)#(lf)                ""schemaType"":""Static""#(cr)#(lf)            },#(cr)#(lf)            {#(cr)#(lf)                ""name"":""TASK NAME"",#(cr)#(lf)                ""FieldName"":""TASK_Name"",#(cr)#(lf)                ""unitText"":null,#(cr)#(lf)                ""columnIndex"":2,#(cr)#(lf)                ""dataType"":""string"",#(cr)#(lf)                ""schemaType"":""Dynamic""#(cr)#(lf) }           #(cr)#(lf)#(cr)#(lf)        ],#(cr)#(lf)        ""rowData"":[#(cr)#(lf)            [#(cr)#(lf)                ""b"",#(cr)#(lf)                ""t1"",#(cr)#(lf)                ""name1""#(cr)#(lf)            ],#(cr)#(lf)            [#(cr)#(lf)                ""b"",#(cr)#(lf)                ""t2"",#(cr)#(lf)                ""name2""#(cr)#(lf)            ],#(cr)#(lf)            [#(cr)#(lf)                ""b"",#(cr)#(lf)                ""t3"",#(cr)#(lf)                ""name3""#(cr)#(lf)            ]#(cr)#(lf)        ]#(cr)#(lf)    }#(cr)#(lf)]",
    #"Parsed JSON" = Json.Document(Source),
    #"Converted to Table" = Table.FromList(#"Parsed JSON", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Added Custom" = Table.AddColumn(#"Converted to Table", "Custom", each Table.FromRows( 
    _[Column1][rowData], 
    List.Transform(
        _[Column1][columnMetadata], 
        (metadata) => metadata[FieldName])
        )),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", List.Union(List.Transform(#"Added Custom"[Custom], Table.ColumnNames) ) ),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Column1"})
in
    #"Removed Columns"

[EvPeCO]

Thanks for the replies and info @mahoneypat and @v-eachen-msft . I'll take a look at both suggestions later today and respond back.

[v-eachen-msft · Accepted solution]

Hi @EvPeCO ,

You could expand the "Column1" with recode. Then you will get an extra column "schemaType".

Now you need to remove rows whose schemaType is "Dynamic" with Table.SelectRows(). At last, remove duplicates.

let
    Source = "[#(cr)#(lf)    {#(cr)#(lf)        ""jobId"":""a"",#(cr)#(lf)        ""columnMetadata"":[#(cr)#(lf)            {#(cr)#(lf)                ""name"":""JOB ID"",#(cr)#(lf)                ""FieldName"":""job_id"",#(cr)#(lf)                ""unitText"":null,#(cr)#(lf)                ""columnIndex"":0,#(cr)#(lf)                ""dataType"":""string"",#(cr)#(lf)                ""schemaType"":""Static""#(cr)#(lf)            },#(cr)#(lf)            {#(cr)#(lf)                ""name"":""TASK ID"",#(cr)#(lf)                ""FieldName"":""TASK_id"",#(cr)#(lf)                ""unitText"":null,#(cr)#(lf)                ""columnIndex"":1,#(cr)#(lf)                ""dataType"":""string"",#(cr)#(lf)                ""schemaType"":""Static""#(cr)#(lf)            }#(cr)#(lf)        ],#(cr)#(lf)        ""rowData"":[#(cr)#(lf)            [#(cr)#(lf)                ""a"",#(cr)#(lf)                ""t1""#(cr)#(lf)            ],#(cr)#(lf)            [#(cr)#(lf)                ""a"",#(cr)#(lf)                ""t2""#(cr)#(lf)            ]#(cr)#(lf)        ]#(cr)#(lf)    },#(cr)#(lf)    {#(cr)#(lf)        ""jobId"":""b"",#(cr)#(lf)        ""columnMetadata"":[#(cr)#(lf)            {#(cr)#(lf)                ""name"":""JOB ID"",#(cr)#(lf)                ""FieldName"":""job_id"",#(cr)#(lf)                ""unitText"":null,#(cr)#(lf)                ""columnIndex"":0,#(cr)#(lf)                ""dataType"":""string"",#(cr)#(lf)                ""schemaType"":""Static""#(cr)#(lf)            },#(cr)#(lf)            {#(cr)#(lf)                ""name"":""TASK ID"",#(cr)#(lf)                ""FieldName"":""TASK_id"",#(cr)#(lf)                ""unitText"":null,#(cr)#(lf)                ""columnIndex"":1,#(cr)#(lf)                ""dataType"":""string"",#(cr)#(lf)                ""schemaType"":""Static""#(cr)#(lf)            },#(cr)#(lf)            {#(cr)#(lf)                ""name"":""TASK NAME"",#(cr)#(lf)                ""FieldName"":""TASK_Name"",#(cr)#(lf)                ""unitText"":null,#(cr)#(lf)                ""columnIndex"":2,#(cr)#(lf)                ""dataType"":""string"",#(cr)#(lf)                ""schemaType"":""Dynamic""#(cr)#(lf) }           #(cr)#(lf)#(cr)#(lf)        ],#(cr)#(lf)        ""rowData"":[#(cr)#(lf)            [#(cr)#(lf)                ""b"",#(cr)#(lf)                ""t1"",#(cr)#(lf)                ""name1""#(cr)#(lf)            ],#(cr)#(lf)            [#(cr)#(lf)                ""b"",#(cr)#(lf)                ""t2"",#(cr)#(lf)                ""name2""#(cr)#(lf)            ],#(cr)#(lf)            [#(cr)#(lf)                ""b"",#(cr)#(lf)                ""t3"",#(cr)#(lf)                ""name3""#(cr)#(lf)            ]#(cr)#(lf)        ]#(cr)#(lf)    }#(cr)#(lf)]",
    #"Parsed JSON" = Json.Document(File.Contents("C:\Users\eadsc\Desktop\New Text Document.json")),
    #"Converted to Table" = Table.FromList(#"Parsed JSON", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Added Custom" = Table.AddColumn(#"Converted to Table", "Custom", each Table.FromRows( 
    _[Column1][rowData], 
    List.Transform(
        _[Column1][columnMetadata], 
        (metadata) => metadata[FieldName])
        )),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", List.Union(List.Transform(#"Added Custom"[Custom], Table.ColumnNames) ) ),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Expanded Custom", "Column1", {"columnMetadata"}, {"Column1.columnMetadata"}),
    #"Expanded Column1.columnMetadata" = Table.ExpandListColumn(#"Expanded Column1", "Column1.columnMetadata"),
    #"Expanded Column1.columnMetadata1" = Table.ExpandRecordColumn(#"Expanded Column1.columnMetadata", "Column1.columnMetadata", {"schemaType"}, {"Column1.columnMetadata.schemaType"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Column1.columnMetadata1", each [Column1.columnMetadata.schemaType] <> "Dynamic"),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Column1.columnMetadata.schemaType"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Columns", {"TASK_Name"})
in
    #"Removed Duplicates"

Here is my test file for your reference.

[EvPeCO]

Thanks @v-eachen-msft We got this solution working. Not sure why we didn't go this path from the get go.  I appreciate you taking the time to provide your expertise and helping us solve this issue.

[mahoneypat]

Your post said you want to filter it out before it gets into the query editor.  The best way to do that is to add a $filter parameter to your API call (or whatever the syntax is for that API).  Are you able to do that?

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat