How to get percentage?

[mfaisalk]

Hello,

I have one column as shown below. I would like to add a second column such that each row is the %age of current traffic out of the aggregate traffic. The aggregate traffic value is infact the sum of all rows. ( question relates to Power query Excel)

Current traffic
23
23
23
17
17
12
11
10
8
8
7
7
6

[AlB · Accepted solution]

@mfaisalk 

See it all at work in the attached file.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ndHdToMwFAfwVznpNRn94Mu+weKNyYw3CxfMnYxmXdtQEB/fVjtlmGg0oQdyaHN+/NnvydyrEeFgX2Gw04gDycgDWqcROu0tdP6cwfbSnRBc9xyen9QRrc9gF45pZc4+HKhoKCKuTcnDjYtriS9YWquL0ZxXOefAGsma2GA5p6khCtJmCfez7dOxZC5Nf/E0cTwXkhbJI2KjlLT54iyyit++mP/d1lvnlDnBgH7SY9jy2E+Xg+mU/q/wLoIqyW6AjElKbwPzVk+jsmZFvDd21nh8j8mgTr8T3IAvCudVhGW01Gl2fS2/AFmKsKg/hKyMDSoLTtr2DQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Keyword = _t, #"SERP features" = _t, Volume = _t, KD = _t, CPC = _t, #"Previous traffic" = _t, #"Current traffic" = _t, #"Traffic change" = _t, #"Previous position" = _t, #"Current position" = _t, #"Position change" = _t, #"Previous URL inside" = _t, #"Previous URL" = _t, #"Current URL inside" = _t, #"Current URL" = _t, #"Previous date" = _t, #"Current date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Keyword", type text}, {"SERP features", type text}, {"Volume", Int64.Type}, {"KD", Int64.Type}, {"CPC", type number}, {"Previous traffic", Int64.Type}, {"Current traffic", Int64.Type}, {"Traffic change", Int64.Type}, {"Previous position", Int64.Type}, {"Current position", Int64.Type}, {"Position change", type text}, {"Previous URL inside", type text}, {"Previous URL", type text}, {"Current URL inside", type text}, {"Current URL", type text}, {"Previous date", type datetime}, {"Current date", type datetime}}),

    allTraffic_ = List.Sum(#"Changed Type"[Current traffic]),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each [Current traffic] / allTraffic_),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", Percentage.Type}})
in
    #"Changed Type1"

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[AlB]

@mfaisalk 

You'll have tomodify the code in the Advanced Editor to add that step. Make sure that you update  #"Changed Type" if necessary to match the name of the previous step

allTraffic_ = List.Sum(#"Changed Type"[Current traffic])

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[mfaisalk]

@AIB, I tried a lot, but could not replicate in my file. Here I am sharing the file, if you can help. The column is "current traffic"

https://docs.google.com/spreadsheets/d/1VDgyeCq55Hnt_BNezCFbjZtu0RXpRkmI/edit?usp=sharing&ouid=10425...

[AlB]

@mfaisalk 

New Source--> Other Source-->Blank Query

Then select the created query and pen the advance editor. Delete the code that appears and paste the code provided in the previous post

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[mfaisalk]

Ok, i pasted the code and analyzing all steps. What is not clear is the Alltraffic step as shown here. How do I replicate this step in my sheet ? This step is shown to occur before the custom column

[mfaisalk]

Thanks, can you guide me or direct me to a resource on how to create query and place the M code. I am very new to the tool.

[AlB]

Hi @mfaisalk 

Place the following M code in a blank query to see the steps.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjJWitVBpwzNUSgjCGUIoQzAlAUSaY5EminFxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Current traffic" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Current traffic", Int64.Type}}),
    allTraffic_ = List.Sum(#"Changed Type"[Current traffic]),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each [Current traffic] / allTraffic_),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", Percentage.Type}})
in
    #"Changed Type1"

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.