Forum Discussion
Replace null value with value from the same column based on value of other column
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.
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.
The value in column B represents an unique ID and column A represent an unique corresponding name. I tried your formule with the addition of distinct on the example data and it worked! But in my real life data it failed to do so, I expected it has to do with the datatype. Cause in my real life data column A is text and not number, do you know how I can make it work when both columns are text?
That would not be the issue, the issue would have to be with the column you are matching, not the value you are replacing nulls with. I would need to see the error message, but some ideas:
1. Case sensitive, are you trying to match on a different casing.
2. There really are multiple same keys with different values.
3. Some issue folding the data. May be related to your datasource kind. If your using SQL see if right click on last step: View native query is there.