Forum Discussion
Solved
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 c...
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.
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
I've just added the table to the post
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.