Forum Discussion
Initial Funding vs Incremental Funding in Query Editor
- 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:
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: