How to subtract if column is having both 'text' and 'number'?

[adhumal2]

Hi All,

I am having a table in power query. I want to subtract old value from new value. However my issue is that my columns have both 'text' and 'number' as shown in image. When i am trying to do this in power query, the subtract column shows error due to data type issues. I want to get the final column as shown in 'Final Remark'. How is this possible in power query? or will you advise DAX?

[BA_Pete · Accepted solution]

Hi @adhumal2 ,

Try this in a new custom column:

let
  oldValue = Number.From([oldValue]),
  newValue = Number.From([newValue]),
  variance = Number.Abs(newValue - oldValue)  
in
try if [oldValue] = [newValue] then "Same"
else if Value.Is(oldValue, type number) and Value.Is(newValue, type number) and newValue < oldValue then Text.From(variance) & " reduced"
else if Value.Is(oldValue, type number) and Value.Is(newValue, type number) and newValue > oldValue then Text.From(variance) & " added"
else null otherwise "Unrated to Rated"

Output:

Full M query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlLSUTJWitWJVjKBs0rzihJLUlOAfBM0PowFUw+RN4XojAUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [oldValue = _t, newValue = _t]),
    addFinalRemark = Table.AddColumn(Source, "finalRemark", each let
  oldValue = Number.From([oldValue]),
  newValue = Number.From([newValue]),
  variance = Number.Abs(newValue - oldValue)  
in
try if [oldValue] = [newValue] then "Same"
else if Value.Is(oldValue, type number) and Value.Is(newValue, type number) and newValue < oldValue then Text.From(variance) & " reduced"
else if Value.Is(oldValue, type number) and Value.Is(newValue, type number) and newValue > oldValue then Text.From(variance) & " added"
else null otherwise "Unrated to Rated")
in
    addFinalRemark

Pete

[adhumal2]

@BA_Pete Thank you very much! It worked nicely.

I have another question - where can we learn powerquery / M code in easy to understand manner? 

[BA_Pete]

Hi @adhumal2 ,

I'd start with Microsoft Learn. It takes you right from beginner to advanced and I *think* it's all free (except for certification exams).

https://docs.microsoft.com/en-us/learn/powerplatform/power-bi 

Other than that, I'd say that just working with Power Query day-to-day will be a good way to learn in an understandable way.

Look for real-world problems that you have, or things you would like to do with your own data, and search the Power Query forums here for related threads. The solutions provided here are usually explained in very clear terms and, when you have a real-world problem that you need to solve, the PQ solution becomes far more relatable and therefore more easily understood and learnt.

Pete

[adhumal2]

@BA_Pete Thank you very much! I agree with you.

[BA_Pete · Accepted solution]

Hi @adhumal2 ,

Try this in a new custom column:

let
  oldValue = Number.From([oldValue]),
  newValue = Number.From([newValue]),
  variance = Number.Abs(newValue - oldValue)  
in
try if [oldValue] = [newValue] then "Same"
else if Value.Is(oldValue, type number) and Value.Is(newValue, type number) and newValue < oldValue then Text.From(variance) & " reduced"
else if Value.Is(oldValue, type number) and Value.Is(newValue, type number) and newValue > oldValue then Text.From(variance) & " added"
else null otherwise "Unrated to Rated"

Output:

Full M query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlLSUTJWitWJVjKBs0rzihJLUlOAfBM0PowFUw+RN4XojAUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [oldValue = _t, newValue = _t]),
    addFinalRemark = Table.AddColumn(Source, "finalRemark", each let
  oldValue = Number.From([oldValue]),
  newValue = Number.From([newValue]),
  variance = Number.Abs(newValue - oldValue)  
in
try if [oldValue] = [newValue] then "Same"
else if Value.Is(oldValue, type number) and Value.Is(newValue, type number) and newValue < oldValue then Text.From(variance) & " reduced"
else if Value.Is(oldValue, type number) and Value.Is(newValue, type number) and newValue > oldValue then Text.From(variance) & " added"
else null otherwise "Unrated to Rated")
in
    addFinalRemark

Pete

[adhumal2]

@BA_Pete Many Thanks for jumping in.

I am getting 'Unrated to Rated' every row. below is the custom column formula . Am i doing anything wrong?

let
oldValue = Number.From([#"Old Value - with OUT"]),
newValue = Number.From([#"New Value - with Out"]),
variance = Number.Abs(newValue - oldValue)
in
try if [oldValue] = [newValue] then "Same"
else if Value.Is(oldValue, type number) and Value.Is(newValue, type number) and newValue < oldValue then Text.From(variance) & "Less"
else if Value.Is(oldValue, type number) and Value.Is(newValue, type number) and newValue > oldValue then Text.From(variance) & "More"
else null otherwise "Unrated to Rated"

[BA_Pete · Accepted solution]

Hi @adhumal2 ,

This bit needs to be changed:

Should be:

try if [#"Old Value - with OUT"] = [#"New Value - with Out"] then "Same"

Pete