Oct 22 2020 11:50 AM
I have a parameter that is a year. Can it be passed to a calculated field? The parameter will always grab the latest year and be subtracted from 2010. So, if I get new data for 2020 the Parameter will grab the latest year and the calculation will use it for the subtraction (2019-2010).
Oct 22 2020 12:28 PM
Absolutely it's possible. I don't see in your spreadsheet any spot where you're trying to do it, however. So it's hard to give anything specific. Let me just offer the following and invite you, if needed, to post a revised spreadsheet where you might include the formula where you're trying to make the calculation and not having it work.
First, though, let's see if a few pointers can help you make it work.
As I said, though, I didn't see in your spreadsheet any attempt at a formula incorporating a parameter such as you describe.
Oct 23 2020 12:15 PM
@mathetes I added the Parameter and the created the calculated column and I want the calculated column to be fed from the parameter
Oct 23 2020 01:42 PM
SolutionIt's not necessary to keep parameter as a table, for only one value named cell is more suitable
It is taken as
// LatestYear
let
Source = Text.From(
Excel.CurrentWorkbook(){[Name="LatestYear"]}[Content][Column1]{0}
)
in
Source
Resulting table
is generated by
// Population with LatestYear
let
Source = Excel.CurrentWorkbook(){[Name="CO_1_10"]}[Content],
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Renamed Columns" = Table.RenameColumns(#"Promoted Headers",{{"Column1", "Locality"}}),
// New part
#"Duplicated Column which has parameter as name" = Table.DuplicateColumn(
#"Renamed Columns",
LatestYear, "Difference"
),
#"Replace Value in it with difference to 2010" = Table.ReplaceValue(
#"Duplicated Column which has parameter as name",
each [Difference],
each [Difference] - [2010],
Replacer.ReplaceValue,{"Difference"}
)
in
#"Replace Value in it with difference to 2010"
hope it is self-explainable.
If you always take the difference from the last column of the table, you may avoid using parameter at all:
let
//Population without Parameter
Source = Excel.CurrentWorkbook(){[Name="CO_1_10"]}[Content],
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Renamed Columns" = Table.RenameColumns(#"Promoted Headers",{{"Column1", "Locality"}}),
// Take last column name
columnNames = Table.ColumnNames(#"Renamed Columns"),
lastColumnName = List.Last(columnNames),
// calculate difference
#"Duplicated Last Column" = Table.DuplicateColumn(
#"Renamed Columns",
lastColumnName, "Difference"
),
#"Replace Value in it with difference to 2010" = Table.ReplaceValue(
#"Duplicated Last Column",
each [Difference],
each [Difference] - [2010],
Replacer.ReplaceValue,{"Difference"}
)
in
#"Replace Value in it with difference to 2010"
In general second column [2010] also could be taken from headers names:
let
//Population without hardcoding
Source = Excel.CurrentWorkbook(){[Name="CO_1_10"]}[Content],
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Renamed Columns" = Table.RenameColumns(#"Promoted Headers",{{"Column1", "Locality"}}),
// Take last column name
columnNames = Table.ColumnNames(#"Renamed Columns"),
firstYearColumnName = columnNames{1},
lastColumnName = List.Last(columnNames),
// calculate difference
#"Duplicated Last Column" = Table.DuplicateColumn(
#"Renamed Columns",
lastColumnName, "Last"
),
#"Duplicated First Column" = Table.DuplicateColumn(
#"Duplicated Last Column",
firstYearColumnName, "First"
),
#"Add Difference" = Table.AddColumn(
#"Duplicated First Column",
"Difference",
each [Last] - [First]
),
#"We don't need First and Last any more" = Table.RemoveColumns(
#"Add Difference",
{"Last", "First"}
)
in
#"We don't need First and Last any more"
Oct 23 2020 01:42 PM
SolutionIt's not necessary to keep parameter as a table, for only one value named cell is more suitable
It is taken as
// LatestYear
let
Source = Text.From(
Excel.CurrentWorkbook(){[Name="LatestYear"]}[Content][Column1]{0}
)
in
Source
Resulting table
is generated by
// Population with LatestYear
let
Source = Excel.CurrentWorkbook(){[Name="CO_1_10"]}[Content],
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Renamed Columns" = Table.RenameColumns(#"Promoted Headers",{{"Column1", "Locality"}}),
// New part
#"Duplicated Column which has parameter as name" = Table.DuplicateColumn(
#"Renamed Columns",
LatestYear, "Difference"
),
#"Replace Value in it with difference to 2010" = Table.ReplaceValue(
#"Duplicated Column which has parameter as name",
each [Difference],
each [Difference] - [2010],
Replacer.ReplaceValue,{"Difference"}
)
in
#"Replace Value in it with difference to 2010"
hope it is self-explainable.
If you always take the difference from the last column of the table, you may avoid using parameter at all:
let
//Population without Parameter
Source = Excel.CurrentWorkbook(){[Name="CO_1_10"]}[Content],
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Renamed Columns" = Table.RenameColumns(#"Promoted Headers",{{"Column1", "Locality"}}),
// Take last column name
columnNames = Table.ColumnNames(#"Renamed Columns"),
lastColumnName = List.Last(columnNames),
// calculate difference
#"Duplicated Last Column" = Table.DuplicateColumn(
#"Renamed Columns",
lastColumnName, "Difference"
),
#"Replace Value in it with difference to 2010" = Table.ReplaceValue(
#"Duplicated Last Column",
each [Difference],
each [Difference] - [2010],
Replacer.ReplaceValue,{"Difference"}
)
in
#"Replace Value in it with difference to 2010"
In general second column [2010] also could be taken from headers names:
let
//Population without hardcoding
Source = Excel.CurrentWorkbook(){[Name="CO_1_10"]}[Content],
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Renamed Columns" = Table.RenameColumns(#"Promoted Headers",{{"Column1", "Locality"}}),
// Take last column name
columnNames = Table.ColumnNames(#"Renamed Columns"),
firstYearColumnName = columnNames{1},
lastColumnName = List.Last(columnNames),
// calculate difference
#"Duplicated Last Column" = Table.DuplicateColumn(
#"Renamed Columns",
lastColumnName, "Last"
),
#"Duplicated First Column" = Table.DuplicateColumn(
#"Duplicated Last Column",
firstYearColumnName, "First"
),
#"Add Difference" = Table.AddColumn(
#"Duplicated First Column",
"Difference",
each [Last] - [First]
),
#"We don't need First and Last any more" = Table.RemoveColumns(
#"Add Difference",
{"Last", "First"}
)
in
#"We don't need First and Last any more"