Forum Discussion
Power Query: after combining 2 tables got something strange
I have a column "Name", in which each value is repeated twice. And each value has its own share (input data). I need to calculate the average share among the first occurrence of values in the first column, and the average for the second occurrence, for each subgroup (the subgroups are distinguished by the first part of the name: like input car 1, car 1, car 2, car 2, bike 1, bike 1, bike 2, bike 2, etc (word + number). An example is shown in the figure. I already created an occurrence index.
| Owner | Name | Share | Index | what I need -> | Owner | Name | Share | Index | Average share |
| Bob | Car 1 | 0,45 | 1 | Bob | Car 1 | 0,45 | 1 | (0,45+0,4)/2 | |
| Bob | Car 1 | 0,55 | 2 | Bob | Car 1 | 0,55 | 2 | (0,55+0,6)/2 | |
| Bob | Car 2 | 0,4 | 1 | Bob | Car 2 | 0,4 | 1 | (0,45+0,4)/2 | |
| Bob | Car 2 | 0,6 | 2 | Bob | Car 2 | 0,6 | 2 | (0,55+0,6)/2 | |
| Alex | Bike 1 | 0,3 | 1 | Alex | Bike 1 | 0,3 | 1 | (0,3+0,2)/2 | |
| Alex | Bike 1 | 0,7 | 2 | Alex | Bike 1 | 0,7 | 2 | (0,7+0,8)/2 | |
| Alex | Bike 2 | 0,2 | 1 | Alex | Bike 2 | 0,2 | 1 | (0,3+0,2)/2 | |
| Alex | Bike 2 | 0,8 | 2 | Alex | Bike 2 | 0,8 | 2 | (0,7+0,8)/2 |
To solve the task, I used "group by" and most of my columns disappeared in the new table. And when I try to merge the tables, I get strange results with many rows being repeated million times.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcspPUtJRck4sUjAE0gY6JqZAylApVgdTyhQkZYQmZQTRhUUTRMYMrscxJ7UCyHHKzE6FGmgM14UpZ45VH8RMI6z6IHIWEH2xAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Owner = _t, Name = _t, Share = _t, Index = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Share", type number}},"de"), #"Added Custom" = Table.AddColumn(#"Changed Type", "Average Share", (k)=> List.Average(Table.SelectRows(#"Changed Type", each [Owner]=k[Owner] and [Index]=k[Index])[Share]),type number) in #"Added Custom"How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the Source step with your own source.
6 Replies
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information or anything not related to the issue or question.
If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523Hi! How can I include the file or send it? I couldn't find such option in the settings of the post
If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Looks like you should be grouping by owner and index. From there, I'm not sure if you want the result to be the result of the calculation (average of the shares of each group) or the actual text
(0,45+0,4)/2 But if you are trying to end up with the actual average, you need to split the share column by the comma and then do your group by owner and index, and then aggregate by average, which seems to be the best way to do this.
--Nate
