How to achieve this in PowerQuery?

[Anonymous]

Hello, 

There is a requirement for me to do calculations in PowerQuery end which I am not sure.

How to do a calculation from here as 444/166 which is 2.67. I can do this easily in DAX but please do let me know how to achieve in PowerQuery?

A column name with Ratio with a value of 2.67.

Thanks,

Karthik

[Samarth_18 · Accepted solution]

HI @Anonymous ,

I am considering you are having only two records in your table. You can apply below steps:-

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8k3MK03MUdJRMjExUYrViVbyy89T0FXIhQkbmpkpxcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Job Category" = _t, Count = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Job Category", type text}, {"Count", Int64.Type}}),
    #"Transposed Table" = Table.Transpose(#"Changed Type"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Manual", Int64.Type}, {"Non - manual", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom", each [Manual] / [#"Non - manual"]),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Added Custom", {"Custom"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns",{{"Attribute", "Job Description"}, {"Custom", "count"}}),
    #"Renamed Columns1" = Table.RenameColumns(#"Renamed Columns",{{"Value", "counts"}, {"count", "Divide"}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns1",{"Job Description", "counts", "Divide"})
in
    #"Reordered Columns"

I have added data using enter data you can update source based on your source.

Thanks,

Samarth

[Anonymous]

Thank you so much @Samarth_18 . Though it seems to be lot of steps in PowerQuery but seems to be achievable. Thanks for your guidance.