Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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 |
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="
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
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".
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.
You can use Value.Is - PowerQuery M | Microsoft Learn for that, or brute force it via "try ... otherwise ..."