Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Initial Funding vs Incremental Funding in Query Editor

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 s...
  • rocky09's avatar
    8 years ago

    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: