Multiply all columns with numbers by -1, skip columns with text in power query

[Syndicate_Admin]

I have data where the first 3 columns are text and the rest are numbers. I want to multiply the columns with numbers by -1 so the text remains. Sample data

Source                                K1 Name:                       EIN                                              Carry Fwd Tax Capital  Tax - Capital Contr

ACTUAL	abc inc	11-1111111	100000	200000
ACTUAL	def inc	22-2222222	200000	300000

results requested:

Source                                K1 Name:                       EIN                                              Carry Fwd Tax Capital  Tax - Capital Contr

ACTUAL	abc inc	11-1111111	-100000	-200000
ACTUAL	def inc	22-2222222	-200000	-300000

[Syndicate_Admin]

Thanks you. I am still learning. Do I need to replace any of the text above, such as below? I don't recognize these

Json.Document

"i45WcnQOCXX0UdJRSkxKVsjMSwayDA11DYEAxDIAAiBtBKZjdZCUp6SmQZUbGekaAQFcmY6SMUR5LAA="

[Anonymous]

Hi @Syndicate_Admin 

As  @lbendlin  said, you just need to create a new query in power bi desktop.

Then put the code i have offered to the advanced editor to this blank query

Best Regards!

Yolo Zhu

[lbendlin]

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".

[Anonymous]

Hi @Syndicate_Admin 

You can put the following code to advanced editor in power query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnQOCXX0UdJRSkxKVsjMSwayDA11DYEAxDIAAiBtBKZjdZCUp6SmQZUbGekaAQFcmY6SMUR5LAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [a = _t, b = _t, c = _t, d = _t, f = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"a", type text}, {"b", type text}, {"c", type text}, {"d", Int64.Type}, {"f", Int64.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {}, "Attribute", "Value"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Columns", "Custom", each if Value.Is([Value],Number.Type) then Int64.From([Value])*(-1) else [Value]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Value"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Attribute"}, {{"Count", each _, type table [Attribute=text, Custom=any]}}),
    #"Added Custom1" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Count],"Index",1,1)),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom1",{"Count"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns1", "Custom", {"Custom", "Index"}, {"Custom.1", "Index"}),
    #"Pivoted Column" = Table.Pivot(#"Expanded Custom", List.Distinct(#"Expanded Custom"[Attribute]), "Attribute", "Custom.1"),
    #"Removed Columns2" = Table.RemoveColumns(#"Pivoted Column",{"Index"})
in
    #"Removed Columns2"

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

You can use Value.Is - PowerQuery M | Microsoft Learn  for that, or brute force it via "try ... otherwise ..."