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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors