Power query custom connector - Default Selected Columns

[Anonymous]

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.

[v-frfei-msft · Accepted solution]

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"