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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
TarynMiller99
New Member

Add a conditional column referencing a separate query

I have 2 queries that do not have a common field

Query 1 shows a list of periods and their corresponding dates eg.

Period 1     01/01/24     31/01/24       

Period 2     01/02/24     28/02/24

etc.

Query 2 shows a list of data each with a transaction date

 

I would like to look up the transaction data from query 2 within query one and tell me which period it falls within.

 

How do I go about this please?

3 ACCEPTED SOLUTIONS
TarynMiller99
New Member

I managed to get it to work asking Chat GPT but there are millions of rows of data so this query is taking too long, do you know how I can speed this up? This is the M Code that Chat GPT gave me

let
// Step 1: Load the necessary tables
Source = Sql.Databases("sansrv-sql.sanctuary.local"),
IntacctCdataSync = Source{[Name="IntacctCdataSync"]}[Data],
dbo_Gldetail = IntacctCdataSync{[Schema="dbo",Item="Gldetail"]}[Data],
dbo_Reportingperiod = IntacctCdataSync{[Schema="dbo",Item="Reportingperiod"]}[Data],

// Step 2: Filter the Gldetail table
FilteredRows = Table.SelectRows(dbo_Gldetail, each [Bookid] = "ACCRUAL"),
#"Filtered Rows" = Table.SelectRows(FilteredRows, each [Entry_date] >= #date(2023, 10, 30)),

// Step 3: Define a custom function to check the date range and return the corresponding Name
GetReportingPeriodName = (entry_date as date) as nullable text =>
let
matchingPeriod = Table.SelectRows(dbo_Reportingperiod, each entry_date >= [Start_date] and entry_date <= [End_date]),
name = if Table.IsEmpty(matchingPeriod) then null else Record.Field(matchingPeriod{0}, "Name")
in
name,

// Step 4: Add a custom column using the custom function
CustomColumn = Table.AddColumn(#"Filtered Rows", "ReportingPeriodName", each GetReportingPeriodName([Entry_date]))
in
CustomColumn

View solution in original post

PwerQueryKees
Super User
Super User

I would expand your table with periods to have a record for every date you need and the period attached.

Either you build the dates by generating the dates you would ever need OR you remove all ithe columns from you accruals table except the date and the remove duplicates. The apply the chatgpt function on the dates and the do aan nestedjoin of your accruals table with the dates table.

Probably a lot quicker, but you will have to test yourself if it is quick enough for your requirements.

View solution in original post

Hi @TarynMiller99 ,

Glad to see you found a solution!
In fact, I took a closer look at your M code, and the way we generally optimize performance is to FILTER out useless data before transformation, which is already done in the code you provided. There is also the option of using the Group by feature to operate on all the data in different groups so that it only processes a portion of the data at a time, but it seems that Group by can't be applied in your case.

There is also the fact that you are currently adding three tables to a single query, you could try loading the three tables into the three queries separately and maybe performance could be optimized a little bit.

Also I see that your data source is SQL, I think the best way to do this at the moment would be to apply query folding or to do the results you need in the database and then import into Power Query. You can try to see if you can do query folding, i.e. converting the query that you need to be done in Power Query to be done in the data source, as the query performance of the database will be much better than that of Power Query.
Understanding query evaluation and query folding in Power Query - Power Query | Microsoft Learn

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

View solution in original post

4 REPLIES 4
PwerQueryKees
Super User
Super User

I would expand your table with periods to have a record for every date you need and the period attached.

Either you build the dates by generating the dates you would ever need OR you remove all ithe columns from you accruals table except the date and the remove duplicates. The apply the chatgpt function on the dates and the do aan nestedjoin of your accruals table with the dates table.

Probably a lot quicker, but you will have to test yourself if it is quick enough for your requirements.

TarynMiller99
New Member

I managed to get it to work asking Chat GPT but there are millions of rows of data so this query is taking too long, do you know how I can speed this up? This is the M Code that Chat GPT gave me

let
// Step 1: Load the necessary tables
Source = Sql.Databases("sansrv-sql.sanctuary.local"),
IntacctCdataSync = Source{[Name="IntacctCdataSync"]}[Data],
dbo_Gldetail = IntacctCdataSync{[Schema="dbo",Item="Gldetail"]}[Data],
dbo_Reportingperiod = IntacctCdataSync{[Schema="dbo",Item="Reportingperiod"]}[Data],

// Step 2: Filter the Gldetail table
FilteredRows = Table.SelectRows(dbo_Gldetail, each [Bookid] = "ACCRUAL"),
#"Filtered Rows" = Table.SelectRows(FilteredRows, each [Entry_date] >= #date(2023, 10, 30)),

// Step 3: Define a custom function to check the date range and return the corresponding Name
GetReportingPeriodName = (entry_date as date) as nullable text =>
let
matchingPeriod = Table.SelectRows(dbo_Reportingperiod, each entry_date >= [Start_date] and entry_date <= [End_date]),
name = if Table.IsEmpty(matchingPeriod) then null else Record.Field(matchingPeriod{0}, "Name")
in
name,

// Step 4: Add a custom column using the custom function
CustomColumn = Table.AddColumn(#"Filtered Rows", "ReportingPeriodName", each GetReportingPeriodName([Entry_date]))
in
CustomColumn

Hi @TarynMiller99 ,

Glad to see you found a solution!
In fact, I took a closer look at your M code, and the way we generally optimize performance is to FILTER out useless data before transformation, which is already done in the code you provided. There is also the option of using the Group by feature to operate on all the data in different groups so that it only processes a portion of the data at a time, but it seems that Group by can't be applied in your case.

There is also the fact that you are currently adding three tables to a single query, you could try loading the three tables into the three queries separately and maybe performance could be optimized a little bit.

Also I see that your data source is SQL, I think the best way to do this at the moment would be to apply query folding or to do the results you need in the database and then import into Power Query. You can try to see if you can do query folding, i.e. converting the query that you need to be done in Power Query to be done in the data source, as the query performance of the database will be much better than that of Power Query.
Understanding query evaluation and query folding in Power Query - Power Query | Microsoft Learn

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

shafiz_p
Super User
Super User

Hi @TarynMiller99   It appears that you’d like to validate whether a transaction date falls within the date range of another query. If it does, you want to retrieve the corresponding period. You can achieve this using the following Power Query code. I’ve used the column names “Min Date” and “Max Date” from Query 1 named "Table":

 

Note : I have Used try and otherwise to prevent error in case of no match. I have returned null, if you want you can change to other value according to your need.


let

Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ31TcyMDJRitUBccwQHCN9I2SOBYJjrG+IrMfQEsqLBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Transaction Date" = _t]),


TypeChanged = Table.TransformColumnTypes(Source,{{"Transaction Date", type date}}),

 


ReturnPeriod = Table.AddColumn(
TypeChanged,
"Period",
each
(
  try
     let
         dates = [Transaction Date],
         filterrows = Table.SelectRows(Table, each dates >= [Min Date] and dates <= [Max Date])
     in
         filterrows{0}[Period]
  otherwise null
),
type text
)

in
ReturnPeriod


Hope this helps!!

If this solved your problem, please mark it as a solution.

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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

Top Solution Authors
Top Kudoed Authors