Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Create a nested table column in a table with date ranges from another table

A little background; I'm trying to consolidate two different data sets. One contains among other things a batch code, start date and end date and the other is simply a huge continuous time stamped log from our climate cell sensor data. The goal is to be able to compare growth success per batch with the conditions in our climate cells. In ideal conditions the sensor data is very predictable. In the end I want to be able to select batch codes and compare the sensor data as relative time series so the start of a new batch is the start of a new "sub" sensor log, so I can superimpose the data in nice graphs.

 

For that I'm trying to simply create a new column where each cell contains a nested table with a subset of the sensor logs pertaining the relevant data.

 

I'm stuck on a single power query statement:

#"RDM data" = Table.AddColumn(#"Added Conditional Column", "RDM", each Table.SelectRows(#"Cel 2 RDM", each [Time] >= [Date] and [Time] <= [#"Harvest day 1"]))

This way it doesn't find the records I call:

 

Expression.Error: The field 'Date' of the record wasn't found.
Details:
    Time=31/12/2018 23:00:00
    Gemiddelde cel temp {Deg. C}=212
    Gemiddelde RV cel {%rH}=410
    Nh3 cel {ppm}=3

I figured it's trying to find the records in the "Cel 2 RDM" table, so I specified the (self) table in the query

 

    #"RDM data" = Table.AddColumn(#"Added Conditional Column", "RDM", each Table.SelectRows(#"Cel 2 RDM", each [Time] >= #"ALIS Cel 2 dates"[Date] and [Time] <= #"ALIS Cel 2 dates"[#"Harvest day 1"])),

 

This is a query I'm doing in the batchcode/daterange table which is called "ALIS Cel 2 dates". This gives me a cyclic reference error.

 

Is Table.SelectRows the wrong way to do this? It should return a table like this right? I thought I had it right with the first statement, that it should return a table with all rows that match my conditions, and then create a new column where it returns a table for each row with a date range. It should be very straightforward but I'm having trouble with understanding how M works with regards to variable scope I think? Any advice would be greatly appreciated.

Need Help
  • dax's avatar
    dax
    7 years ago

    Hi pwildmann,

    I think you need to merge two tables into one by using merge queries or  function like below

    Table.SelectRows( Categories , (Magic) => Text.Contains( [LandingPage] , Magic[KeyPhrase] ) ), Then you could try create a custom column by compare each row

    You could refer to PowerQuery(M)agic: Conditional Joins using Table.SelectRows() for details.

    Best Regards,
    Zoe Zhi

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • dax's avatar
    dax
    Icon for Community Support rankCommunity Support
    7 years ago

    Hi pwildmann,

    I think you need to merge two tables into one by using merge queries or  function like below

    Table.SelectRows( Categories , (Magic) => Text.Contains( [LandingPage] , Magic[KeyPhrase] ) ), Then you could try create a custom column by compare each row

    You could refer to PowerQuery(M)agic: Conditional Joins using Table.SelectRows() for details.

    Best Regards,
    Zoe Zhi

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      It worked great, thanks! So if I understand correctly by explicitly defining the function instead of using each you can then reference back to the original table through the function from within Table.SelectRows because the function originated in the original table?

       

          #"RDM data" = Table.AddColumn(#"Added Conditional Column", "RDM", (magic) => Table.SelectRows(#"Cel 2 RDM", each [Time] >= magic[#"Date + time"] and DateTime.Date([Time]) <= DateTime.Date(magic[#"Harvest day 1"])))

      This did the trick. Still some messy type conversion, I know, but I'll fix that later. I'm new to M and it's not like any other programming or query language I've used before, I was stuck on this one for longer than I care to admit. Most things still feel a little counter intuitive at times.