Forum Discussion
Power query custom connector - Default Selected Columns
I have a table with 100 + columns, by default most of the return null unless we tell the underlying service to return a value for them.
So I want 'Choose Columns' to only show as selected the handful of columns that are returned by default and then I can use OnSelectColumns to fetch the other columns as required.
So far I can not work out how to get Choose Columns to show anything other than all columns as selected initially.
Hi Anonymous ,
Please check the following steps as below.
1. Transpose the whole table.
2. Add a custom column like this.if [Column1] = "" and [Column2]="" then 1 else 0
2. Filter the table by the custom column , make the value = 0.
3. transpose the new table again and filter the last row out.
M code for your reference.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUYIgKKtYKVYHWRgqChSOBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [a = _t, b = _t, c = _t, d = _t, e = _t, f = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"a", Int64.Type}, {"b", type text}, {"c", type text}, {"d", Int64.Type}, {"e", type text}, {"f", type text}}), #"Transposed Table" = Table.Transpose(#"Changed Type"), #"Added Custom" = Table.AddColumn(#"Transposed Table", "Custom", each if [Column1] = "" and [Column2]="" then 1 else 0), #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = 0)), #"Transposed Table1" = Table.Transpose(#"Filtered Rows"), #"Filtered Rows1" = Table.SelectRows(#"Transposed Table1", each ([Column1] = 1)) in #"Filtered Rows1"
1 Reply
v-frfei-msftCommunity Support
Hi Anonymous ,
Please check the following steps as below.
1. Transpose the whole table.
2. Add a custom column like this.if [Column1] = "" and [Column2]="" then 1 else 0
2. Filter the table by the custom column , make the value = 0.
3. transpose the new table again and filter the last row out.
M code for your reference.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUYIgKKtYKVYHWRgqChSOBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [a = _t, b = _t, c = _t, d = _t, e = _t, f = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"a", Int64.Type}, {"b", type text}, {"c", type text}, {"d", Int64.Type}, {"e", type text}, {"f", type text}}), #"Transposed Table" = Table.Transpose(#"Changed Type"), #"Added Custom" = Table.AddColumn(#"Transposed Table", "Custom", each if [Column1] = "" and [Column2]="" then 1 else 0), #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = 0)), #"Transposed Table1" = Table.Transpose(#"Filtered Rows"), #"Filtered Rows1" = Table.SelectRows(#"Transposed Table1", each ([Column1] = 1)) in #"Filtered Rows1"