Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Tjerk
Regular Visitor

Record.ToList not all values from record end up in list

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

 

1 ACCEPTED SOLUTION
MarcelBeug
Community Champion
Community Champion

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.

Specializing in Power Query Formula Language (M)

View solution in original post

4 REPLIES 4
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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:

Record

 

while the output of the Stdev_List is

List

 

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

MarcelBeug
Community Champion
Community Champion

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.

Specializing in Power Query Formula Language (M)
Tjerk
Regular Visitor

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.