Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
mfaisalk
Regular Visitor

How to get percentage?

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
1 ACCEPTED SOLUTION
AlB
Super User
Super User

@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"

SU18_powerbi_badge

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.

 

View solution in original post

7 REPLIES 7
AlB
Super User
Super User

@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"

SU18_powerbi_badge

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
Super User
Super User

@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])

 

SU18_powerbi_badge

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.

 

 

@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
Super User
Super User

@mfaisalk 

 

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

AlB_0-1672073059204.png

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

AlB_1-1672073159277.png

 

SU18_powerbi_badge

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.

 

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

 

DowonsN

mfaisalk
Regular Visitor

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
Super User
Super User

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"

 

SU18_powerbi_badge

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.

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors