Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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
Solved! Go to 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.
the underscore _ is a shortcut for "current row"
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"}))))
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
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
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
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
24 | |
12 | |
12 | |
11 | |
8 |
User | Count |
---|---|
42 | |
25 | |
14 | |
14 | |
12 |