Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe 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.
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?
Solved! Go to Solution.
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
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.
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.
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.
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.
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.