Initial Funding vs Incremental Funding in Query Editor

[Anonymous]

I have a table with 4 columns: Project ID, Project Name, Date, Funding Amount. A project is initially funded, then depending how it goes, it will get incremental funding.  Is there a way that I can see each project inital funding and incremental funding amounts using Query Editor? 

[Ashish_Mathur]

Hi,

Share a dataset and show the expected result.

[rocky09 · Accepted solution]

I would do like this,

First I will duplicate the table then, I will group the duplicated tabled Group By -> Project Name, and Aggreation by Count of rows.

then go to the Advanced editor, then i will just replace the List.count to List.First. So, you will get First Values each Project. Then rename the column to Initial Amount

Now, I will select First table and then selece Merge queries and then Iwill select Project Names in both table and while in expand, I will just select Initial Amount. Now, You will have table with initial amount.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bdA9D8IgEAbgv0KIIzFAodRRh25ONfGj6YApSV1qgjL033smCCc63PJentxH31NBGT1B7e/zaBdGWnf1wfqFiIoRyYWBHllpvuac0IH1VEJwhjoE9yiEQkLyTCpILlBHN84/SGOkklEQbN9jpuBLUuPNMtFxTOtvBTAYyATqeHtnn8EXpMFr6URMXKsL5bs2XyAf30Cy+/dgyZFQn3cNLw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Project ID" = _t, #" Project Name" = _t, #" Date" = _t, #" Funding Amount" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Project ID", Int64.Type}, {" Project Name", type text}, {" Date", type date}, {" Funding Amount", Currency.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {" Project Name"}, {{"Count", each List.First([#" Funding Amount"]), type number}}),
    #"Renamed Columns" = Table.RenameColumns(#"Grouped Rows",{{"Count", "Initial Fund"}})
in
    #"Renamed Columns"

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bdA9D8IgEAbgv0KIIzFAodRRh25ONfGj6YApSV1qgjL033smCCc63PJentxH31NBGT1B7e/zaBdGWnf1wfqFiIoRyYWBHllpvuac0IH1VEJwhjoE9yiEQkLyTCpILlBHN84/SGOkklEQbN9jpuBLUuPNMtFxTOtvBTAYyATqeHtnn8EXpMFr6URMXKsL5bs2XyAf30Cy+/dgyZFQn3cNLw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Project ID" = _t, #" Project Name" = _t, #" Date" = _t, #" Funding Amount" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Project ID", Int64.Type}, {" Project Name", type text}, {" Date", type date}, {" Funding Amount", Currency.Type}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type",{" Project Name"},#"Table1 (2)",{" Project Name"},"Table1 (2)",JoinKind.LeftOuter),
    #"Expanded Table1 (2)" = Table.ExpandTableColumn(#"Merged Queries", "Table1 (2)", {"Initial Fund"}, {"Initial Fund"})
in
    #"Expanded Table1 (2)"

Results:

[Anonymous]

@rocky09,

Thank you so much. You are really a life saver!

[rocky09]

@Anonymous

You are welcome 🙂