Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
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.
Solved! Go to Solution.
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.
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.
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.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
72 | |
71 | |
56 | |
38 | |
35 |
User | Count |
---|---|
85 | |
66 | |
59 | |
46 | |
45 |