Dividing a value among each row based on a different column as the primary key

[Anonymous]

Hi Guys, I have a data set where there is a primary key column and a price column. The price column is repeated for the same primary key. Instead, I want the price column to be divided into each primary key equally. Please, someone, guide me here, thank you.

[v-xiaotang · Accepted solution]

Hi @Anonymous 

You can try this, in Power Query Editor, 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUbIwUorVwcU0AjINTQ1Q2bEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"QCF #" = _t, #"QCF $" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"QCF $", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"QCF #"}, {{"c1", each Table.RowCount(_), Int64.Type}, {"c2", each _, type table [#"QCF #"=nullable text, #"QCF $"=nullable number]}}),
    #"Expanded c2" = Table.ExpandTableColumn(#"Grouped Rows", "c2", {"QCF $"}, {"c2.QCF $"}),
    #"Added Custom" = Table.AddColumn(#"Expanded c2", "Custom", each [#"c2.QCF $"]/[c1]),
    #"Removed Duplicates" = Table.Distinct(#"Added Custom"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Duplicates",{{"Custom", type number}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"c1"})
in
    #"Removed Columns"

Steps:

(1) 

(2) 

(3) 

(4) 

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thank you, I have fixed the issue using Excel, but this was helpful

[lbendlin]

"The price column is repeated for the same primary key"

That means your primary key is not a primary key.  A primary key is unique across all rows.

Please provide sanitized sample data that fully covers your issue. If you paste the data into a table in your post or use one of the file services it will be easier to work with. Avoid posting screenshots of your source data if possible.