Forum Discussion
Replace null value with value from the same column based on value of other column
Hi guys, can someone help me out with the following problem?
Column A Colum B
null abc
null abc
123 abc
456 efg
null efg
Right now I have something like this (the bold part is where I cannot figure it out in power query way):
#"Value replace" = Table.ReplaceValue(#"Last step", null, each if [Colum B] <> null
then [Column A] = Value from Column A given they have the exact same value in Column B else [Column A]
,Replacer.ReplaceValue,{"Column A"})
So if it were to work correctly, the null value in column A should be replaced by either 123 or 456
Much Appreciated!
Yea, sorry, it is kind of condenced.
Table.ReorderColumns(Table.RenameColumns(Table.RemoveColumns(Table.AddColumn(PreviousStep, "_temp", each Table.SelectRows(PreviousStep, each [ColumnA] <> null){[[ColumnB]]}[ColumnA]), "ColumnA"), {"_temp", "ColumnA"}), Table.ColumnNames(PreviousStep))ReorderColumns, RenameColumns, RemoveColumns, AddColumns - this it just to get around the fact that when you do a transform columns operations you can't access other columns.
- Table.SelectRows(PreviousStep, ...) - this is to create a sub query which has unique values for the columns we are interested in.
- each [ColumnA] <> null - this removes the null entries as part of getting unique values in the ColumnB
- "each <expression>" is shorthand for: (_ as any) as any => <expression>
- (foo as text, bar as number) as duration => means declare a function that takes in for and bar as parameters and returns duration with implementation after =>
- [ColumnA] is shorthand for: _[ColumnA] where _ is the function variable
- {[[ColumnB]]} -
- [[ColumnB]] is shorthand for [ColumnB=_[ColumnB]], which means a record with field ColumnB equal to the _ variable's field ColumnB. Or in other words, just take the ColumnB part of the row record. [ColumnB] means unwrap the field, while [[ColumnB]] means don't unwrap it.
- MyTable{[KeyColumn="MyValue"]} means get the row from MyTable where the column KeyColumn has the value "MyValue"
- ...{[[ColumnB]]}[ColumnA] - means after getting the row that where ColumnB matches ColumB of the previous Table.Select statement, get the value of ColumnA.
- MyTable[ColumnA] means get the column ColumnA for MyTable.
- MyTable{0} means get the the first row from MyTable.
- MyRow[ABC] means get the record field ABC from the record MyRow
- MyColumn{0} means get the first entry from a list
- MyTable[ColumnA]{0} = MyTable{0}[ColumnA]
- The final part about Table.ColumnNames() is just to restore the order of the columns back to their origional order.
8 Replies
You can add this step to do it:
= Table.ReorderColumns(Table.RenameColumns(Table.RemoveColumns(Table.AddColumn(PreviousStep, "_temp", each Table.SelectRows(PreviousStep, each [ColumnA] <> null){[[ColumnB]]}[ColumnA]), "ColumnA"), {"_temp", "ColumnA"}), Table.ColumnNames(PreviousStep))Thanks for your help. Although it only managed to replace the first group of column A where they have the same value in column B, then it fails to for the other set so null-abc succesfully changed to 123-abc but null efg turned into error - efg. The error received is Expression error: the key matches with multiple rows in the table
This will happen if there are more than 1 value that efg maps too. If they both map to the same, you can add a Table.Distinct() around the Table.SelectColumns function. If they have different values then you will need to determine the expected behvariour in that case.
