Insert new columns with value as 0 in case they don't exist already

[PC2790]

Hello,

I am dealing with a situation of loading the data from multiple columns which may or may not be available everytime.

This is the reason the further transformation steps are failing in case if there is even a single column unavailable.

So as a workaround, I am thinking of adding the column as a dummy column with values as '0'

For example I have columns names as 6 hope- H1,H2,H3,H4,H5 and H6. 

Can anyone please help me with the power query with the logic to check if any of the columns with the above mentioned column names is missing, add a new column with value as 0.

Thanks in advance for your help

[Jakinta · Accepted solution]

This might help...

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("HYvJDQAgEAJ74e1DvK1ls/23IfgYQiYQAaKgiS4GsgSm2hJbnG+uGqvDa7Yv6QeHww8uZD4=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [H1 = _t, H2 = _t, H3 = _t, H6 = _t]),
    Cols = {"H1","H2","H3","H4","H5","H6"},
    Missing = List.Difference(Cols, Table.ColumnNames(Source)),
    AllRows = List.Transform( Table.ToRows(Source), each _ & List.Repeat({0}, List.Count(Missing))),
    Table = Table.FromRows( AllRows, Table.ColumnNames(Source) & Missing),
    Sorted = Table.ReorderColumns( Table, List.Sort( Table.ColumnNames( Table ), Order.Ascending ))
in
    Sorted

[Vera_33 · Accepted solution]

Hi @PC2790 

Another way, based on @Jakinta solution, if you can use null, stop at Custom,

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("HYvJDQAgEAJ74e1DvK1ls/23IfgYQiYQAaKgiS4GsgSm2hJbnG+uGqvDa7Yv6QeHww8uZD4=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [H1 = _t, H2 = _t, H3 = _t, H6 = _t]),
    Cols = {"H1","H2","H3","H4","H5","H6"},
    Missing = List.Difference(Cols, Table.ColumnNames(Source)),
    Custom = Table.Combine({ #table(Cols,{}),Source}),
    #"Replaced Value" = Table.ReplaceValue(Custom,null,0,Replacer.ReplaceValue,Missing)
in
    #"Replaced Value"

[PC2790]

Thanks @Vera_33 

I tried this. The only problem I am facing here is that it is not replacing all null in all the missing columns.

It is just doing only for the first column in the missing list.

I am not sure what I am doing wrong here.

[Vera_33]

Hi @PC2790 

I was using the sample from @Jakinta , H4 and H5 are missing.

Cols contains all the columns you want, Missing to find the missing columns. Mine was creating a table with full Cols then append with your current table which contains missing columns

[Jakinta · Accepted solution]

This might help...

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("HYvJDQAgEAJ74e1DvK1ls/23IfgYQiYQAaKgiS4GsgSm2hJbnG+uGqvDa7Yv6QeHww8uZD4=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [H1 = _t, H2 = _t, H3 = _t, H6 = _t]),
    Cols = {"H1","H2","H3","H4","H5","H6"},
    Missing = List.Difference(Cols, Table.ColumnNames(Source)),
    AllRows = List.Transform( Table.ToRows(Source), each _ & List.Repeat({0}, List.Count(Missing))),
    Table = Table.FromRows( AllRows, Table.ColumnNames(Source) & Missing),
    Sorted = Table.ReorderColumns( Table, List.Sort( Table.ColumnNames( Table ), Order.Ascending ))
in
    Sorted

[PC2790]

Thankyou so much.

It works like magic.

Just one thing more, Is there any way I can maintain the list as a separate query(as in Blank Query) and reference in my table for as you are doing it in the below step?

Missing = List.Difference(Cols, Table.ColumnNames(Source)),