Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
I have a data set uploaded to my PowerQuery called "MRS version"
Here, there is a table looking like this:
MRS Start | MRS End | MRS version |
01/01/2023 | 08/02/2023 | 1 |
09/02/2023 | 05/03/2023 | 2 |
06/03/2023 | 03/04/2023 | 3 |
In another data set i have uploaded called "Raw data" i have a column called "Latest Booking Submission Date".
I would like to know which version of the MRS connects to the date in "Latest Booking Submission Date".
Example: If Latest Booking Submission Date is the 15/02/2023 then it returns that this is MRS Version 2.
I have tried multiple solutions which has resulted in the entire program to crash, so i am looking for a 'simple' and not to heavy solution to this. Hope there is someone that can help me!
Solved! Go to Solution.
Hi @crn009 ,
Thanks for jgeddes reply.
Here is my sample data
Raw data
Latest Booking Submission Date |
2/15/2023 |
2/1/2023 |
4/1/2023 |
You can try the following code
let
MRSVersion = #"MRS version",
RawData = #"Raw data",
Addcustom = Table.AddColumn(RawData, "MRSVersion", each
let
submissionDate = [Latest Booking Submission Date],
matchingRow = Table.SelectRows(MRSVersion, each _[MRS Start] <= submissionDate and _[MRS End] >= submissionDate)
in
if Table.IsEmpty(matchingRow) then null else matchingRow{0}[MRS version]
)
in
Addcustom
Final output
Best regards,
Albert He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hi @crn009, check also this:
let
T1 = [ a = Date.From(DateTime.LocalNow()),
b = List.Generate(
()=> { Date.AddDays(a, -12000), Date.AddDays(a, -11994) },
each _{0} <= a,
each { Date.AddDays(_{0}, 7), Date.AddDays(_{1}, 7) } ),
c = Table.AddIndexColumn(Table.FromRows(b, type table[MRS Start=date, MRS End=date]), "MRS Version", 1)
][c],
T1_SortedRows = Table.Sort(T1,{{"MRS Start", Order.Ascending}}),
T1_Ad_Date = Table.AddColumn(T1_SortedRows, "Date", each List.Dates([MRS Start], Duration.TotalDays([MRS End]-[MRS Start])+1, #duration(1,0,0,0)), type {date}),
// This table has to be uniqe per [Date]
T1_ExpandedDate = Table.ExpandListColumn(T1_Ad_Date, "Date"),
// Do not add any new columns after this step to T1 (do it before this step if necessary)
T1_AddKey = Table.AddKey(Table.ReorderColumns(T1_ExpandedDate, {"Date"} & List.RemoveItems(Table.ColumnNames(T1_ExpandedDate), {"Date"})), {"Date"}, true),
T2 = [ a = Date.From(DateTime.LocalNow()),
b = Date.AddDays(a, -10000),
c = Table.FromList(List.Dates(b, Duration.TotalDays(a-b)+1, #duration(1,0,0,0)), Splitter.SplitByNothing(), type table[D=date]),
d = Table.AddIndexColumn(c, "i", Number.From(Table.FirstN(c, 1){0}[D])),
e = Table.AddColumn(d, "Date", each Date.From(Number.Round(Number.RandomBetween(d{0}[i], Table.LastN(d, 1){0}[i]))), type date)[[Date]]
][e],
MergedQueries = Table.NestedJoin(T2, {"Date"}, T1_AddKey, {"Date"}, "T1", JoinKind.LeftOuter),
ExpandedT1 = Table.ExpandTableColumn(MergedQueries, "T1", {"MRS Version"}, {"MRS Version"})
in
ExpandedT1
Hi @crn009, check also this:
let
T1 = [ a = Date.From(DateTime.LocalNow()),
b = List.Generate(
()=> { Date.AddDays(a, -12000), Date.AddDays(a, -11994) },
each _{0} <= a,
each { Date.AddDays(_{0}, 7), Date.AddDays(_{1}, 7) } ),
c = Table.AddIndexColumn(Table.FromRows(b, type table[MRS Start=date, MRS End=date]), "MRS Version", 1)
][c],
T1_SortedRows = Table.Sort(T1,{{"MRS Start", Order.Ascending}}),
T1_Ad_Date = Table.AddColumn(T1_SortedRows, "Date", each List.Dates([MRS Start], Duration.TotalDays([MRS End]-[MRS Start])+1, #duration(1,0,0,0)), type {date}),
// This table has to be uniqe per [Date]
T1_ExpandedDate = Table.ExpandListColumn(T1_Ad_Date, "Date"),
// Do not add any new columns after this step to T1 (do it before this step if necessary)
T1_AddKey = Table.AddKey(Table.ReorderColumns(T1_ExpandedDate, {"Date"} & List.RemoveItems(Table.ColumnNames(T1_ExpandedDate), {"Date"})), {"Date"}, true),
T2 = [ a = Date.From(DateTime.LocalNow()),
b = Date.AddDays(a, -10000),
c = Table.FromList(List.Dates(b, Duration.TotalDays(a-b)+1, #duration(1,0,0,0)), Splitter.SplitByNothing(), type table[D=date]),
d = Table.AddIndexColumn(c, "i", Number.From(Table.FirstN(c, 1){0}[D])),
e = Table.AddColumn(d, "Date", each Date.From(Number.Round(Number.RandomBetween(d{0}[i], Table.LastN(d, 1){0}[i]))), type date)[[Date]]
][e],
MergedQueries = Table.NestedJoin(T2, {"Date"}, T1_AddKey, {"Date"}, "T1", JoinKind.LeftOuter),
ExpandedT1 = Table.ExpandTableColumn(MergedQueries, "T1", {"MRS Version"}, {"MRS Version"})
in
ExpandedT1
1. Rename "MRS Start" column to "Latest Booking Submission Date"
2. Combine 2 tables (MTS_version & Raw_data)
3. Sort resulting table by "Latest Booking Submission Date", then by "MRS End" (both Order.Ascending)
4. Fill down "MRS version" column
5. Filter out rows from MRS_version table (MRS End is null) and remove "MRS End" column
Hi @crn009 ,
Thanks for jgeddes reply.
Here is my sample data
Raw data
Latest Booking Submission Date |
2/15/2023 |
2/1/2023 |
4/1/2023 |
You can try the following code
let
MRSVersion = #"MRS version",
RawData = #"Raw data",
Addcustom = Table.AddColumn(RawData, "MRSVersion", each
let
submissionDate = [Latest Booking Submission Date],
matchingRow = Table.SelectRows(MRSVersion, each _[MRS Start] <= submissionDate and _[MRS End] >= submissionDate)
in
if Table.IsEmpty(matchingRow) then null else matchingRow{0}[MRS version]
)
in
Addcustom
Final output
Best regards,
Albert He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hi Albert He,
Thank you very much for your help!
Although, i have been down the path you mention here and many others - the problem keeps being that by dataset is very heavy, so the PowerBI is crashing over and over again after loading approx 10k rows..
Do you have any solution that can prevent that from happening?
Hi @crn009 ,
You can try these following methods:
First, if possible, try to reduce the amount of data loaded. For example, load only the necessary columns or use filters to reduce the number of rows. Or process the dataset in smaller parts. You can use parameterized queries or partitioning strategies to load data in segments. Of course, you can also try to increase your computer's memory if you can to improve processing efficiency.
Second, you can use the Table.Buffer function to cache the table data and reduce the overhead of repeated calculations.
let
MRSVersion = #"MRS version",
RawData = #"Raw data",
BufferedMRSVersion = Table.Buffer(MRSVersion),
Addcustom = Table.AddColumn(RawData, "MRSVersion", each
let
submissionDate = [Latest Booking Submission Date],
matchingRow = Table.SelectRows(BufferedMRSVersion, each _[MRS Start] <= submissionDate and _[MRS End] >= submissionDate)
in
if Table.IsEmpty(matchingRow) then null else matchingRow{0}[MRS version]
)
in
Addcustom
Best practices when working with Power Query - Power Query | Microsoft Learn
Best regards,
Albert He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
You can try the following logic to see if it will work in your situation.
Add a column with this code (changing it to match your column/table names).
= Table.AddColumn(#"Changed Type", "MRS Version", each let rowDate = [subDate] in Record.Field(Table.SelectRows(Table, each rowDate >= [MRS Start] and rowDate <= [MRS End]){0}, "MRS version"))
This code should be added in the table that you are looking to return the 'MRS Version' to.
#"Changed Type" is the name of the previous step in the table.
[subDate] is the name of the column in the table that has the date value you are wanting to filter the date table with. (You referred to it as 'Latest Booking Submission Date'.)
In the 'Table.SelectRows() function...
Table is the name of your date table.
[MRS Start] & [MRS End] are the names of the respective columns in the date table.
{0} returns the first row of the resulting table.
Record.Field() takes the first row of the filtered table and returns the value from the 'MRS version' column.
Hope this gets you pointed in the right direction.
Proud to be a Super User! | |
Check out the July 2025 Power BI update to learn about new features.