Forum Discussion
Solved
Filtering out columns in M
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)...
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.
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