Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
I am trying to use the record.tolist function to populate a list with values that i use to calculate a standard deviation
#"Stdev list" = Table.AddColumn(Select_Data_Columns, "Stdev_List", each Record.ToList(Record.SelectFields(_,Select_Data_Columns))),
The problem is that the code above only results in a list with the first 20 values from the record
whilst the code beow results in a record with 29 values
#"Stdev record" = Table.AddColumn(Select_Data_Columns, "Stdev_Record", each Record.SelectFields(_,Select_Data_Columns)),
I am still a relative PQ novice so I can't figure out what's wrong here. Is there some sort of limit to the maximum number of values in a list?
I hope somebody can help!
Kind Regards,
Tjerk
Solved! Go to Solution.
I think the answer from Bill Szysz to your cross post in Technet is plausible (basically the list preview only shows 20 items while the actual lists have more items).
At least worthwhile for you to react on Technet and - if you start cross posting - you should also cross reference.
Hi @Tjerk,
Can you please share some sample data for test? It is hard to check/modify your formula without any data.
BTW, if your data contains any privacy data, please do mask sensitive data or create some fake data with similar data structure.
Regards,
Xiaoxin Sheng
I have a table with product data. The first couple of columns are some identifiers such as the productid, and the productcategory. Next to those i have 30 columns that contain a price for a specific day. I want to calculate the standard deviation of the price for those 30 days.
My full code is below
let Source = Csv.Document(File.Contents("C:\Users\xxxx\Desktop\query_result (45).csv"),[Delimiter=",", Columns=35, Encoding=1252, QuoteStyle=QuoteStyle.None]), #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]), #"Filtered Rows" = Table.SelectRows(#"Promoted Headers", each ([globalid] = "xxxx")), #"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows",{{"globalid", type text}, {"issuetype", type text}, {"unit", type text}, {"category", type text}, {"views", Int64.Type}, {"a20170910", type number}, {"a20170911", type number}, {"a20170912", type number}, {"a20170913", type number}, {"a20170914", type number}, {"a20170915", type number}, {"a20170916", type number}, {"a20170917", type number}, {"a20170918", type number}, {"a20170919", type number}, {"a20170920", type number}, {"a20170921", type number}, {"a20170922", type number}, {"a20170923", type number}, {"a20170924", type number}, {"a20170925", type number}, {"a20170926", type number}, {"a20170927", type number}, {"a20170928", type number}, {"a20170929", type number}, {"a20170930", type number}, {"a20171001", type number}, {"a20171002", type number}, {"a20171003", type number}, {"a20171004", type number}, {"a20171005", type number}, {"a20171006", type number}, {"a20171007", type number}, {"a20171008", type number}, {"a20171009", type number}}), Select_Exclude_Columns = List.Buffer(List.Select(Table.ColumnNames(#"Changed Type"), each Text.Start(_,1) <> "a")), Select_Data_Columns = List.Buffer(List.Select(Table.ColumnNames(#"Changed Type"), each Text.Start(_,1) = "a")), #"Stdev list" = Table.AddColumn(#"Changed Type", "Stdev_List", each Record.ToList( Record.SelectFields(_,Select_Data_Columns))), #"Stdev record" = Table.AddColumn(#"Stdev list", "Stdev_Record", each Record.SelectFields(_,Select_Data_Columns)) in #"Stdev record"
The problem i run into is that the output of Stdev_Record is:
while the output of the Stdev_List is
As you can see the last 9 numbers that are present in the record are missing in the list. I can't seem to figure out why....
I think the answer from Bill Szysz to your cross post in Technet is plausible (basically the list preview only shows 20 items while the actual lists have more items).
At least worthwhile for you to react on Technet and - if you start cross posting - you should also cross reference.
Hi,
I am trying to use the record.tolist function to populate a list with values that i use to calculate a standard deviation
#"Stdev list" = Table.AddColumn(Select_Data_Columns, "Stdev_List", each Record.ToList(Record.SelectFields(_,Select_Data_Columns))),
The problem is that the code above only results in a list with the first 20 values from the record
whilst the code beow results in a record with 29 values
#"Stdev record" = Table.AddColumn(Select_Data_Columns, "Stdev_Record", each Record.SelectFields(_,Select_Data_Columns)),
I am still a relative PQ novice so I can't figure out what's wrong here. Is there some sort of limit to the maximum number of values in a list?
I hope somebody can help!
Kind Regards,
Tjerk
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
118 | |
101 | |
71 | |
61 |