Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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 ..."