Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to Solution.
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.
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.
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.
Hi, I read the post and I have the same issue. I replicate this example, hoewever the following error messagem was showed:
Expression.Error: The name 'PreviousStep' wasn't recognized. Make sure it's spelled correctly.
@artemus Could you help me?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.