cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
osama_ayoub
Helper I
Helper I

add minimum column of some of columns which selected dynamically

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  

 

osama_ayoub_0-1664556390828.png

 

1 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.

View solution in original post

17 REPLIES 17
lbendlin
Super User
Super User

the underscore _  is a shortcut for "current row" 

alannavarro
Resolver I
Resolver I

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

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 

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"

 

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
Helper I
Helper I

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 

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.

 

 

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 

List.Min(list1)

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

osama_ayoub_0-1664803430288.png

 

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

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"

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.

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.

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.

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
Super User
Super User

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

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors