add minimum column of some of columns which selected dynamically

[osama_ayoub]

Hi,

I have a query and I want to create new column which calculate the minimum of 6 columns dynamically,

I create a list of these 6 columns and when I try to refer to them in the equation of minimum column, I can't get the minimum of numbers in these 6 columns , instead of that I get name of column

these is the equation I wrote := Table.AddColumn(#"Changed Type", "Minimum", each List.Min(#"Last 6 months"), Int64.Type)

Thanks for help  

[lbendlin]

the underscore _  is a shortcut for "current row" 

[alannavarro]

Maybe Im wrong... I tried to do it this way to get always the last 6 columns and then apply the listmin 

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("RVLJkcRACMvF7300N8QyNfmnsZZweT5UGyyQBJ/PZRLX3+XRd+ycOyozZYVMJd4jyI9f37/PJaL3lxTqpXjH4C3a7IS3C6JlEZFJfA97oaK1GaCHMyTYyWYR7Gsub98gus8vHwpceBBh5JvhqPPf4aR2xCj0UPLIXB3Oig46ahgQAeVNVl7IiDk1PTOUf/WvUnSsqcYVaKM78cxoQz17PT7UjChU0AlW0tChZ71yoYLyV20RF0IvyLN1PT7LirtL5tKBaGpqOipJVkbfZnUs36H+Ptydk+cZ8sG8IcJnZ8wkJwYdo6PJuoGPHSqr9TOJKD/0Zx09/Bea1IO70/cKRp+dD+pyeA18606ljtLzbjZ73Z31ardmQX/oK7nvnmzV6LPzvYy9Ce+ngqvsZwe4fKp83G129+TWiky4TeEtORFK7+eO3+8/", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Store 1" = _t, #"Store 2" = _t, #"Store 3" = _t, #"Store 4" = _t, #"Store 5" = _t, #"Store 6" = _t, #"Store 7" = _t, #"Store 8" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Store 1", Int64.Type}, {"Store 2", Int64.Type}, {"Store 3", Int64.Type}, {"Store 4", Int64.Type}, {"Store 5", Int64.Type}, {"Store 6", Int64.Type}, {"Store 7", Int64.Type}, {"Store 8", Int64.Type}}),
Custom1 = Table.AddColumn(#"Changed Type", "Min" , each List.Min(List.LastN( Record.ToList(_),6)))
in
Custom1

[osama_ayoub]

thanks for your help 

my question is about the last function (list.min) , what I need is how can I write the argument of this function when I want to refer to another list which has multiple column names ?

thanks again for your help 

[lbendlin]

If you really want to use the "current record" shortcut (which I would advise against) it would be more like

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("RVLJkcRACMvF7300N8QyNfmnsZZweT5UGyyQBJ/PZRLX3+XRd+ycOyozZYVMJd4jyI9f37/PJaL3lxTqpXjH4C3a7IS3C6JlEZFJfA97oaK1GaCHMyTYyWYR7Gsub98gus8vHwpceBBh5JvhqPPf4aR2xCj0UPLIXB3Oig46ahgQAeVNVl7IiDk1PTOUf/WvUnSsqcYVaKM78cxoQz17PT7UjChU0AlW0tChZ71yoYLyV20RF0IvyLN1PT7LirtL5tKBaGpqOipJVkbfZnUs36H+Ptydk+cZ8sG8IcJnZ8wkJwYdo6PJuoGPHSqr9TOJKD/0Zx09/Bea1IO70/cKRp+dD+pyeA18606ljtLzbjZ73Z31ardmQX/oK7nvnmzV6LPzvYy9Ce+ngqvsZwe4fKp83G129+TWiky4TeEtORFK7+eO3+8/", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Store 1" = _t, #"Store 2" = _t, #"Store 3" = _t, #"Store 4" = _t, #"Store 5" = _t, #"Store 6" = _t, #"Store 7" = _t, #"Store 8" = _t]),
    #"Changed Type" = Table.TransformColumns(Source,{},Int64.From),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Minimum", each List.Min(Record.ToList(_)),Int64.Type)
in
    #"Added Custom"

[jbwtp]

If we already have a list of columns and want to use them this is how this can be done (on the last line above): 

Table.AddColumn(#"Changed Type", "Min" , each List.Min(Record.ToList(Record.SelectFields(_, {"Store 6", "Store 7"}))))

[osama_ayoub]

Thanks for your help:

here a sample of what I need :

 Sheetshttps://docs.google.com/spreadsheets/d/1_UWWE5pZdhdgIsmCxueDqt_RjP01V816/edit#gid=1319292092 

 what I need is a column can calculate the minimum of some other selected columns, but theses columns are selected dynamically be creating a list of all stores and choose last 3 , so when I update the data, power query will update the last 3 stores with new ones and so on:

I already used this equation but it does not work 

= Table.AddColumn(#"Changed Type", "Minimum", each List.Min(#"Last 6 months"), Int64.Type)

Regards 

[lbendlin]

This code will do what you ask for.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("RVLJkcRACMvF7300N8QyNfmnsZZweT5UGyyQBJ/PZRLX3+XRd+ycOyozZYVMJd4jyI9f37/PJaL3lxTqpXjH4C3a7IS3C6JlEZFJfA97oaK1GaCHMyTYyWYR7Gsub98gus8vHwpceBBh5JvhqPPf4aR2xCj0UPLIXB3Oig46ahgQAeVNVl7IiDk1PTOUf/WvUnSsqcYVaKM78cxoQz17PT7UjChU0AlW0tChZ71yoYLyV20RF0IvyLN1PT7LirtL5tKBaGpqOipJVkbfZnUs36H+Ptydk+cZ8sG8IcJnZ8wkJwYdo6PJuoGPHSqr9TOJKD/0Zx09/Bea1IO70/cKRp+dD+pyeA18606ljtLzbjZ73Z31ardmQX/oK7nvnmzV6LPzvYy9Ce+ngqvsZwe4fKp83G129+TWiky4TeEtORFK7+eO3+8/", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Store 1" = _t, #"Store 2" = _t, #"Store 3" = _t, #"Store 4" = _t, #"Store 5" = _t, #"Store 6" = _t, #"Store 7" = _t, #"Store 8" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Store 1", Int64.Type}, {"Store 2", Int64.Type}, {"Store 3", Int64.Type}, {"Store 4", Int64.Type}, {"Store 5", Int64.Type}, {"Store 6", Int64.Type}, {"Store 7", Int64.Type}, {"Store 8", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Minimum", each List.Min(Record.ToList(#"Changed Type"{[Index]}))),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Minimum", Int64.Type}})
in
    #"Changed Type1"

Be advised that data like yours needs to be treated before it can be loaded to Power BI. You need an index column, and ideally you would unpivot your data.

[osama_ayoub]

thanks for your help 

I already created a list of the required column names (say its name list1)

what I need is how to refer to this list in function (list.min() )

Regards 

[lbendlin]

List.Min(list1)

[osama_ayoub]

this is what I got :

insted of take the column names from the list (list1) and calculate the minimum,

the query take the values stored in (list1) and tried to get the minimum value of these names

[lbendlin]

show the definition for list1.  Should be something like List.FromRecord(_)

[osama_ayoub]

this is the code i used to create a list of column names 

1-first I used function Table.ColumnNames()

2-second I used function list.lastN()

this is my code 

let
Source = Excel.CurrentWorkbook(){[Name="Table5"]}[Content],
#"Removed Columns" = Table.RemoveColumns(Source,{"minimum store"}),
Custom1 = Table.ColumnNames(#"Removed Columns"),
#"Kept Last Items" = List.LastN(Custom1, 6)
in
#"Kept Last Items"

[lbendlin]

You don't need a list of column names. You need a list of column values.

Please provide sanitized sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

[osama_ayoub]

try to download the file below and do not open it with google sheets 

what I want is to create a power query to calculate the minimum of last 6 stores this is a normal function but what I need is dynamic last 6 stores , I mean every time I update the raw data and refresh the query I get the minimum of last 6 stores, so I thought I will create a list using list.LastN and refer to it in function list.min()

if you have another approach you can say it but remeber what essentially I need is dynamic last 6 months 

thank you for your patience and support 

Regards.

[lbendlin · Accepted solution]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("RVLJkcRACMvF7300N8QyNfmnsZZweT5UGyyQBJ/PZRLX3+XRd+ycOyozZYVMJd4jyI9f37/PJaL3lxTqpXjH4C3a7IS3C6JlEZFJfA97oaK1GaCHMyTYyWYR7Gsub98gus8vHwpceBBh5JvhqPPf4aR2xCj0UPLIXB3Oig46ahgQAeVNVl7IiDk1PTOUf/WvUnSsqcYVaKM78cxoQz17PT7UjChU0AlW0tChZ71yoYLyV20RF0IvyLN1PT7LirtL5tKBaGpqOipJVkbfZnUs36H+Ptydk+cZ8sG8IcJnZ8wkJwYdo6PJuoGPHSqr9TOJKD/0Zx09/Bea1IO70/cKRp+dD+pyeA18606ljtLzbjZ73Z31ardmQX/oK7nvnmzV6LPzvYy9Ce+ngqvsZwe4fKp83G129+TWiky4TeEtORFK7+eO3+8/", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Store 1" = _t, #"Store 2" = _t, #"Store 3" = _t, #"Store 4" = _t, #"Store 5" = _t, #"Store 6" = _t, #"Store 7" = _t, #"Store 8" = _t]),
     #"Changed Type" = Table.TransformColumns(Source,{},Int64.From),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Minimum Last Six", each List.Min(List.LastN(Record.ToList(_),6)),Int64.Type)
in
    #"Added Custom"

My previous comment still stands.  Your source data is in a format that is not suitable for Power BI. You should add an index column and/or unpivot your data.

[osama_ayoub]

Ok 

I got I need to unpivot data,

I have a final question :

function :Record.ToList(_) ,I understand it 

Returns a list of values containing the field values from the input record.

what is meant by (_) , should this approach return a last 6 store in everytime I update the table 

I tried to do this but it does not work 

thank you 

[lbendlin]

Please provide sanitized sample data that fully covers your issue.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523