Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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 |
Solved! Go to Solution.
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. |
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. |
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. |
@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"
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. |
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
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.
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. |
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
65 | |
61 | |
21 | |
18 | |
12 |