can power query do this?

[WavePkjnm242]

Hi,

I'm not even sure if this is something I should be trying to pull off in the query editor, but I figured I'd throw it out there and see.

I have a list of contributions, along with the fundraisers who were involved in securing those contributions. I want to divide each contribution evenly among the number of fundraisers associated with it and create a new row for each split of the contribution for each fundraiser.

From this to this...

Any thoughts?

Sample data here.

Thanks!

Nick

[BekahLoSurdo · Accepted solution]

Hi @WavePkjnm242,

Here's what I did:

let
    Source = Excel.Workbook(File.Contents("C:\Users\rlosurdo\Desktop\DAX & PQ Training\Training Solutions\data.xlsx"), null, true),
    data_Sheet = Source{[Item="data",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(data_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Donor ID", Int64.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Donor ID", "Contribution ID", "Contribution Amt"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns",{{"Value", "Fundraiser"}}),
    #"Added Contribution Amount" = Table.AddColumn(#"Renamed Columns", "Contribution Amount", each [Contribution Amt] / (let group = [Contribution ID] in List.Count(Table.SelectRows(#"Renamed Columns", each [Contribution ID] = group) [Contribution ID]) )),
    #"Removed Columns" = Table.RemoveColumns(#"Added Contribution Amount",{"Attribute", "Contribution Amt"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Donor ID", "Contribution ID", "Contribution Amount", "Fundraiser"})
in
    #"Reordered Columns"

You will have to change your Source; let me know if you have any questions...

Hope this helps,

Bekah

[Anonymous · Accepted solution]

See the attached pbix, but the steps are:

• Unpivot columns
• Filter out blanks in the new value columns
• Group the rows by Contribution ID, Aggregating by All Row, Count Row, and the Average of Contribution Amount
• Divide Contribution Amouut by Count of people for a new column
• Remove misc Columns
• Expand out
• Rename columns and set data types

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjYzNVLSUTIEYQMgANJO+UlA0qe0ILMkEchwTE8E0yAUqxOtZGFuaglkg3QZQXUgK0EoNLW0MAWyjUFGm0IUeuVn5MFtQFJqZmlqAWSbgJQaQ5T6AY0szkhEaEJSDnQ0SDnIdKjBQflFlZgOAam1NLMwB7LNYB5EUu1Tmgw2PzE5uzgfZEVwRmZRJpLdsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Donor ID" = _t, #"Contribution ID" = _t, #"Contribution Amt" = _t, #"Fundraiser 1" = _t, #"Fundraiser 2" = _t, #"Fundraiser 3" = _t, #"Fundraiser 4" = _t, #"Fundraiser 5" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Donor ID", Int64.Type}, {"Contribution ID", Int64.Type}, {"Contribution Amt", Int64.Type}, {"Fundraiser 1", type text}, {"Fundraiser 2", type text}, {"Fundraiser 3", type text}, {"Fundraiser 4", type text}, {"Fundraiser 5", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Donor ID", "Contribution ID", "Contribution Amt"}, "Attribute", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Other Columns", each ([Value] <> "")),
    #"Reordered Columns" = Table.ReorderColumns(#"Filtered Rows",{"Contribution ID", "Donor ID", "Contribution Amt", "Attribute", "Value"}),
    #"Grouped Rows" = Table.Group(#"Reordered Columns", {"Contribution ID"}, {{"All Data", each _, type table [Contribution ID=number, Donor ID=number, Contribution Amt=number, Attribute=text, Value=text]}, {"Count of People", each Table.RowCount(_), type number}, {"Cont Amount", each List.Average([Contribution Amt]), type number}}),
    #"Inserted Division" = Table.AddColumn(#"Grouped Rows", "Fund Raiseer Share", each [Cont Amount] / [Count of People], type number),
    #"Removed Columns" = Table.RemoveColumns(#"Inserted Division",{"Count of People", "Cont Amount"}),
    #"Expanded All Data" = Table.ExpandTableColumn(#"Removed Columns", "All Data", {"Donor ID", "Contribution Amt", "Attribute", "Value"}, {"Donor ID", "Contribution Amt", "Attribute", "Value"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded All Data",{{"Value", "FundRaiser"}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Renamed Columns",{"Attribute", "Contribution Amt"})
in
    #"Removed Columns1"

Shouldnt be a need to have the total amounts, as you would want to utilize DAX for that type of thing

File:

https://1drv.ms/u/s!Amqd8ArUSwDS3UronyJrRc07H74K?e=2CHWTs

[WavePkjnm242]

Magnificent. Thank you and BekahLoSurdo for the solutions (despite my difficulty in merely posting an image of what I was looking for). A great experience for my first time posting here.

Nick

[BekahLoSurdo · Accepted solution]

Hi @WavePkjnm242,

Here's what I did:

let
    Source = Excel.Workbook(File.Contents("C:\Users\rlosurdo\Desktop\DAX & PQ Training\Training Solutions\data.xlsx"), null, true),
    data_Sheet = Source{[Item="data",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(data_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Donor ID", Int64.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Donor ID", "Contribution ID", "Contribution Amt"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns",{{"Value", "Fundraiser"}}),
    #"Added Contribution Amount" = Table.AddColumn(#"Renamed Columns", "Contribution Amount", each [Contribution Amt] / (let group = [Contribution ID] in List.Count(Table.SelectRows(#"Renamed Columns", each [Contribution ID] = group) [Contribution ID]) )),
    #"Removed Columns" = Table.RemoveColumns(#"Added Contribution Amount",{"Attribute", "Contribution Amt"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Donor ID", "Contribution ID", "Contribution Amount", "Fundraiser"})
in
    #"Reordered Columns"

You will have to change your Source; let me know if you have any questions...

Hope this helps,

Bekah

[WavePkjnm242]

Here's the image that's missing from the initial post...