Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I am trying to replace negative values with Blank (no values) for a particular column. I wrote the M query in Advanced editor.It is not throwing any error butt the vales are not replaced as well.What am I missing here? Below is my code:
#"Replaced Value2" = Table.ReplaceValue(#"Sorted Rows1",each [Sorted Rows1],each if [Sorted Rows1] < 0 then "" else [Sorted Rows1],Replacer.ReplaceValue,{"Sorted Rows1"}),
Solved! Go to Solution.
Your syntax is wrong again. The final arguement has to be a list.
Try this, if if that doesn't work, rename the column "Value" then do the replacement using an exact copy of my formula above. Look at the full syntax of what I've typed in bold red above. Fields are in brackets [ ] and lists are in curly brackets { }. And Table.ReplaceValue expects fields and lists in the right place. You can get Power Query to accept your statement with no errors, but it will not do what you expect, and will likely do nothing.
#"Replaced Value2" = Table.ReplaceValue(#"Trimmed Text",each [Pumping Duration (min)],each if [Pumping Duration (min)] < 0 then "" else [Pumping Duration (min)],Replacer.ReplaceValue,{"Pumping Duration (min)"},
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingThis works for me:
= Table.ReplaceValue(#"Changed Type",each [Value], each if [Value] < 0 then "" else [Value] ,Replacer.ReplaceValue,{"Value"})
Ensure your [Sorted Rows1] column is numeric. If it is text, the replacement will not happen.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingSorry,I am missing something.. What does the first parameter (SortedRows1) even signify?
And what does "Changed Type" mean in your query?
In your original post you had this:
#"Replaced Value2" = Table.ReplaceValue(#"Sorted Rows1",each [Sorted Rows1],each if [Sorted Rows1] < 0 then "" else [Sorted Rows1],Replacer.ReplaceValue,{"Sorted Rows1"}),
You have two things called "Sorted Rows1" which I didn't quite understand why, but I assumed you knew. That is probably wrong though.
For the part of the function that says:
Table.ReplaceValue(#"Sorted Rows1",
#"Sorted Rows1" is referring to the previous step, and is a table. Table.ReplaceValue accepts a table.
For the each [Sorted Rows1] part, that is asking for a field in the aformentioned table. You probably have no field called [Sorted Rows1] so it does nothing, which is why you have no change.
So in my function:
I have more info here, including a sample Excel file so you can play with it.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingI changed the query to this.
#"Replaced Value2" = Table.ReplaceValue(#"Sorted Rows1",each {"Pumping Duration (min)"},each if {"Pumping Duration (min)"} < 0 then "" else {"Pumping Duration (min)"},Replacer.ReplaceValue,{"Pumping Duration (min)"}),
This passes syntactically but the negative values are still not replaced . Not sure what I am missing.
Note: I wanted to work on the already created column and not create a new conditional column to achieve this logic. and that is why trying the above approach.
Note: I wanted to work on the already created column and not create a new conditional column to achieve this logic. and that is why trying the above approach.
Did you see the Excel file I included in my link? I i didn't create a new conditional column. It acted on the existing column. And your syntax may not produce an error, but your are passing lists to the function, not fields. It won't work.
The full M code is:
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Test", type text}, {"Value", Int64.Type}}), #"Replaced Value" = Table.ReplaceValue(#"Changed Type",each [Value], each if [Value] < 0 then "" else [Value] ,Replacer.ReplaceValue,{"Value"}) in #"Replaced Value"
You cannot arbitrarly substitute [ ] with { }. Those mean very different things to Power Query.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingSo if I chge the { to [,it gives me the error "Invalid identifier" for the [Pumping Duration (min)], field.
#"Replaced Value2" = Table.ReplaceValue(#"Trimmed Text",each [Pumping Duration (min)],each if [Pumping Duration (min)] < 0 then "" else [Pumping Duration (min)],Replacer.ReplaceValue,[Pumping Duration (min)],
Note: "Trimmed Text" is from the previous step.
Hi @Anonymous
Your syntax is wrong again. The final arguement has to be a list.
Try this, if if that doesn't work, rename the column "Value" then do the replacement using an exact copy of my formula above. Look at the full syntax of what I've typed in bold red above. Fields are in brackets [ ] and lists are in curly brackets { }. And Table.ReplaceValue expects fields and lists in the right place. You can get Power Query to accept your statement with no errors, but it will not do what you expect, and will likely do nothing.
#"Replaced Value2" = Table.ReplaceValue(#"Trimmed Text",each [Pumping Duration (min)],each if [Pumping Duration (min)] < 0 then "" else [Pumping Duration (min)],Replacer.ReplaceValue,{"Pumping Duration (min)"},
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingUser | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
117 | |
101 | |
71 | |
61 |