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

  • Question

  • 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

         
    Friday, December 1, 2017 10:20 AM

Answers

  • Hi Tjerk,

    You have all of these 30 values...  but in the data preview window (bottom of the main window of Power Query) you can see only 20 of them. Try to click on one of lists in the "Stdev_List" column and see what happen. 

    Cheers

    Monday, December 4, 2017 12:47 PM

All replies

  • Strange. If there would be a maximum, it would definitely not be 20 items.

    It is also strange that you use Select_Data_Columns both as table name (first argument of Table.AddColumn) and as list of required fields (second argument of Record.SelectFields). That just can't work.

    Please take another look at your code and provide more details (preferable some (fake) data) for us to help you further.

    Friday, December 1, 2017 1:19 PM
  • Hi Marcel,

    I have a table with product data. The first couple of columns are some identifiers such as the product_id, and the product_category. 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"

    (your comment about the Select_Data_Columns reference is absolutely true. I modified the code a bit in the first post to make it legible)

    The problem i run into is that the output of Stdev_Record is:

    https://i.imgur.com/3BrkN8M.jpg 

    While the output of the Stdev_List is

    https://i.imgur.com/mS0tkGq.jpg

    (I am not able to post images on this board just yet)

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


    • Edited by Tjerk_db Monday, December 4, 2017 8:19 AM
    Monday, December 4, 2017 8:19 AM
  • Thanks for the clarification with the images.

    Unfortunately I have no explication either.
    It looks like something illogical / technical.

    As an alternative you may try and use Record.FieldValues instead of Record.ToList.

    Monday, December 4, 2017 11:24 AM
  • Hi Marcel,

    Thank you for your reply! Unfortunately the Record.FieldValues also only returns 20 items...
    I wonder what might be going on here.

    Monday, December 4, 2017 11:58 AM
  • Hi Tjerk,

    You have all of these 30 values...  but in the data preview window (bottom of the main window of Power Query) you can see only 20 of them. Try to click on one of lists in the "Stdev_List" column and see what happen. 

    Cheers

    Monday, December 4, 2017 12:47 PM