Log In
Register
Home
Financial Modelling
August 30, 2020
Offline
Hi people! Could you share your way of writing parameter for choosing any column to filter necessary rows in the table inside a custom function.
See Answer
October 5, 2010
Offline
Hi,
I'm not really sure what you mean by 'your way of writing parameter', maybe you could explain more clearly with an example?
However, if you are asking how to write a function and supply parameters to it, that is, send values from a column to it, see the attached file.
What you need to do is:
1. Write your function
2. Add Column -> Invoke Custom Function -> Select function and input column (parameter)
Regards
Phil
Financial Modelling
August 30, 2020
Offline
Hi Philip, thx for reply,
I mean I'm writing custom function and the part of it is selecting necessary rows - = Table.SelectRows(#"Changed Type", each ([Country] = "YourValue")) - however when I provide table the column to be filtered can be different, in this table it is a country, in another table it can be a City etc
for example (sourceTable as table, YourValue as text) => here is two parameters but I'm thinking about the third parameter to make [Country] column name dynamic - it must be any column I choose not only Country
October 5, 2010
Offline
Hi,
It would be much easier if you supplied a detailed example with data in a file. A couple of lines taken out of context are hard to relate to.
If the column you want to filter can be different each time, how do you know how to refer to that column? If you don't know how to refer to it, how do you supply that identifier to your function? You'll need to know either the name or the position of the column.
You could just use
(sourceTable as table, ColName as text, YourValue as text) =>
I'm also not clear on why you appear to be writing a function to replicate an existing function. Maybe I'm missing something but as I said at the start, a clear example with data would help me out.
Regards
Phil
Financial Modelling
August 30, 2020
Offline
Here is code
(Source as table,Value as text)=>
let
#"Grouped Rows" = Table.Group(Source, {"City"}, {{"Total", each List.Sum([Sales]), type number}}),
#"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each ([City] = Value)),
Custom1 = #"Filtered Rows"{0}[Total]
in
Custom1
This code will run only on tables with City and Sales columns, but what is a way to do it dynamic to group other column if needed , I can type Column as text to put inside Table.Group function inside a list {"City"} but it will not work with each ([City] = Value) part because of square brackets I suppose, how to refer to list in square brackets in custom function?
October 5, 2010
Offline
Hi,
I'm having trouble understanding what you want to do but I think it is something like this
(Source as table, Col as text, Value as text)=>
let
#"Grouped Rows" = Table.Group(Source, Col, {{"Total", each List.Sum([Sales]), type number}}),
#"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each (Record.Field(_, Col) = Value)),
Custom1 = #"Filtered Rows"{0}[Total]
in
Custom1
Using this you can specify either the City or the Country and the function will work out the % total of sales by City/Country. See attached.
Regards
Phil
Answers Post
Financial Modelling
August 30, 2020
Offline
Thank you very much, it is what I meant.
October 5, 2010
Offline
No worries 🙂
Financial Modelling
August 30, 2020
Offline
I also did it more dynamic if other users will search for solution it can be helpful for them:
(Source as table, Column as text, Value as text, ColumnToSum as text )=>
let
#"Grouped Rows" = Table.Group(Source, Column, {{"Total", each List.Sum(Table.Column(_, ColumnToSum)), type number}}),
#"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each (Record.Field(_, Column) = Value)),
Custom1 = #"Filtered Rows"{0}[Total]
in
Custom1
1 Guest(s)
