PowerQuery - Replace values in Dynamic Columns

Question

I have a table as below. Only the first column "Ticket No" is constant. For the other columns, depending on the data source i can have multiple columns of a similar nature as the screenshot below (in the screenshot there are only 2 columns but there can be more such columns depending on data source)

What i need is for each dynamic column that appears based on the data source apart from the 1st column, to check if the value in each row is having a value or if its null. If it is having a value, then to replace with 1. If its null then to replace with 0

Current Table

Expected Outcome

horseyride · Accepted Answer · 2024-03-22 16:08:27Z

3

You can try this in powerquery to apply a transform to each column except the first

let  Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Columns = List.RemoveFirstN(Table.ColumnNames(Source),1),
Transform = List.Transform(Columns, (x)=>{x, each if _=null then 0 else 1, type number}),
#"Fix" = Table.TransformColumns(Source, Transform)
in #"Fix"

edited Mar 22, 2024 at 16:08

answered Mar 22, 2024 at 15:56

horseyride

21.8k3 gold badges15 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Anushka A Over a year ago

I have one more modification. In the above, once the 1'sand 0's are populated, i need a new column that will add the values in the dynamic columns. Would you know how to create this ?

horseyride Over a year ago

Add column .. custom column ... with =List.Sum(List.RemoveFirstN(Record.FieldValues(_),1))

Collectives™ on Stack Overflow

PowerQuery - Replace values in Dynamic Columns

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related