Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

The Power BI Data Visualization World Championships is back! Get ahead of the game and start preparing now! Learn more

Reply
Richard_Halsall
Helper IV
Helper IV

Fill missing dates in rows based on value from another table

‎02-22-2024 03:37 AM

Hi, after searching the help topics I am still requiring help to complete what would seem a simple requirement.

I have 2 tables as shown containing Payroll Dates and Contractor Payroll Dates

Payroll Dates


PayrollDatePayrollDayNumber
28/01/20241
29/01/20242
30/01/20243
31/01/20244
01/02/20245
02/02/20246
03/02/20247
04/02/20248
05/02/20249
06/02/202410
07/02/202411
08/02/202412
09/02/202413
10/02/202414

Contractor Payroll Dates

Contractor__cSite_Start_Date__c
a02Q3000001H6IeIAK29/01/2024
a02Q3000001H6IeIAK30/01/2024
a02Q3000001H6IeIAK31/01/2024
a02Q3000001H6IeIAK01/02/2024
a02Q3000001H6IeIAK02/02/2024
a02Q3000001H6IeIAK03/02/2024
a02Q3000001H6IeIAK04/02/2024
a02Q3000001H6IeIAK05/02/2024
a02Q3000001H6IeIAK06/02/2024
a02Q3000001H6IeIAK07/02/2024
a02Q3000001H6IHIA004/02/2024
a02Q3000001H6IHIA005/02/2024
a02Q3000001H6IHIA006/02/2024

 

I am trying to create a table that combines both tables so I have the ability to identify missing payroll dates to look like this

Contractor__cSite_Start_Date__cPayrollDatePayrollDayNumber
a02Q3000001H6IeIAK 28/01/20241
a02Q3000001H6IeIAK29/01/202429/01/20242
a02Q3000001H6IeIAK30/01/202430/01/20243
a02Q3000001H6IeIAK31/01/202431/01/20244
a02Q3000001H6IeIAK01/02/202401/02/20245
a02Q3000001H6IeIAK02/02/202402/02/20246
a02Q3000001H6IeIAK03/02/202403/02/20247
a02Q3000001H6IeIAK04/02/202404/02/20248
a02Q3000001H6IeIAK05/02/202405/02/20249
a02Q3000001H6IeIAK06/02/202406/02/202410
a02Q3000001H6IeIAK07/02/202407/02/202411
a02Q3000001H6IeIAK 08/02/202412
a02Q3000001H6IeIAK 09/02/202413
a02Q3000001H6IeIAK 10/02/202414
a02Q3000001H6IHIA0 28/01/20241
a02Q3000001H6IHIA0 29/01/20242
a02Q3000001H6IHIA0 30/01/20243
a02Q3000001H6IHIA0 31/01/20244
a02Q3000001H6IHIA0 01/02/20245
a02Q3000001H6IHIA0 02/02/20246
a02Q3000001H6IHIA0 03/02/20247
a02Q3000001H6IHIA004/02/202404/02/20248
a02Q3000001H6IHIA005/02/202405/02/20249
a02Q3000001H6IHIA006/02/202406/02/202410
a02Q3000001H6IHIA0 07/02/202411
a02Q3000001H6IHIA0 08/02/202412
a02Q3000001H6IHIA0 09/02/202413
a02Q3000001H6IHIA0 10/02/202414


Any help would be much appreciated. Thanks

Labels:
Message 1 of 3
477 Views
0
1 ACCEPTED SOLUTION
dufoq3
Super User
Super User

‎02-22-2024 04:10 AM

Hi @Richard_Halsall 

 

You have to replace first two steps with your table references. If you don't know how to do it - check comments below.

 

Result:

dufoq3_1-1708603775792.png

let
    PayrollDates = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Tc/LCUAhEEPRXmYtmBn/tYj9t/HE8CDLnFXu3hYzw3MgqiVzO+nSEopHBUKF5EL10d2InxophDqpCA1SFZqkJrRIXchBG2r8j6nGACw1FjjUbsL5AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [PayrollDate = _t, PayrollDayNumber = _t]),
    ContractorPayrollDates = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSjQwCjQ2AAFDDzPPVE9HbyUdJSNLfQNDfSMDIxOlWB0caowNiFBjSFgNUIGBESE1RkSoMSZCjQkRakyJUGNGhBpzPGo8PB0NCLkHpgafe2BqkNwTCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Contractor__c = _t, Site_Start_Date__c = _t]),
    ChangedTypePayrollDates = Table.TransformColumnTypes(PayrollDates,{{"PayrollDate", type date}, {"PayrollDayNumber", Int64.Type}}, "sk-SK"),
    ChangedTypeContractorPayrollDates = Table.TransformColumnTypes(ContractorPayrollDates,{{"Site_Start_Date__c", type date}}),
    GroupedRows = Table.Group(ChangedTypeContractorPayrollDates, {"Contractor__c"}, {{"All", each _, type table [Contractor__c=nullable text, Site_Start_Date__c=nullable date]}}),
    Ad_PayrollDates = Table.AddColumn(GroupedRows, "PayrollDates", each ChangedTypePayrollDates, type table),
    ExpandedPayrollDates = Table.ExpandTableColumn(Ad_PayrollDates, "PayrollDates", {"PayrollDate", "PayrollDayNumber"}, {"PayrollDate", "PayrollDayNumber"}),
    Ad_SiteStartDateC = Table.AddColumn(ExpandedPayrollDates, "Site_Start_Date__c", each if List.Contains([All][Site_Start_Date__c], [PayrollDate]) then [PayrollDate] else null, type date),
    RemovedColumns = Table.RemoveColumns(Ad_SiteStartDateC,{"All"}),
    ReorderedColumns = Table.ReorderColumns(RemovedColumns,{"Contractor__c", "Site_Start_Date__c", "PayrollDate", "PayrollDayNumber"})
in
    ReorderedColumns

Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

View solution in original post

Message 2 of 3
457 Views
2 REPLIES 2
dufoq3
Super User
Super User

‎02-22-2024 04:10 AM

Hi @Richard_Halsall 

 

You have to replace first two steps with your table references. If you don't know how to do it - check comments below.

 

Result:

dufoq3_1-1708603775792.png

let
    PayrollDates = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Tc/LCUAhEEPRXmYtmBn/tYj9t/HE8CDLnFXu3hYzw3MgqiVzO+nSEopHBUKF5EL10d2InxophDqpCA1SFZqkJrRIXchBG2r8j6nGACw1FjjUbsL5AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [PayrollDate = _t, PayrollDayNumber = _t]),
    ContractorPayrollDates = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSjQwCjQ2AAFDDzPPVE9HbyUdJSNLfQNDfSMDIxOlWB0caowNiFBjSFgNUIGBESE1RkSoMSZCjQkRakyJUGNGhBpzPGo8PB0NCLkHpgafe2BqkNwTCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Contractor__c = _t, Site_Start_Date__c = _t]),
    ChangedTypePayrollDates = Table.TransformColumnTypes(PayrollDates,{{"PayrollDate", type date}, {"PayrollDayNumber", Int64.Type}}, "sk-SK"),
    ChangedTypeContractorPayrollDates = Table.TransformColumnTypes(ContractorPayrollDates,{{"Site_Start_Date__c", type date}}),
    GroupedRows = Table.Group(ChangedTypeContractorPayrollDates, {"Contractor__c"}, {{"All", each _, type table [Contractor__c=nullable text, Site_Start_Date__c=nullable date]}}),
    Ad_PayrollDates = Table.AddColumn(GroupedRows, "PayrollDates", each ChangedTypePayrollDates, type table),
    ExpandedPayrollDates = Table.ExpandTableColumn(Ad_PayrollDates, "PayrollDates", {"PayrollDate", "PayrollDayNumber"}, {"PayrollDate", "PayrollDayNumber"}),
    Ad_SiteStartDateC = Table.AddColumn(ExpandedPayrollDates, "Site_Start_Date__c", each if List.Contains([All][Site_Start_Date__c], [PayrollDate]) then [PayrollDate] else null, type date),
    RemovedColumns = Table.RemoveColumns(Ad_SiteStartDateC,{"All"}),
    ReorderedColumns = Table.ReorderColumns(RemovedColumns,{"Contractor__c", "Site_Start_Date__c", "PayrollDate", "PayrollDayNumber"})
in
    ReorderedColumns

Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

Message 2 of 3
458 Views
Richard_Halsall
Helper IV
Helper IV
In response to dufoq3

‎02-22-2024 04:22 AM

Many thanks worked perfectly

Message 3 of 3
447 Views

Helpful resources

Announcements
Power BI DataViz World Championships

Power BI Dataviz World Championships

The Power BI Data Visualization World Championships is back! Get ahead of the game and start preparing now!

December 2025 Power BI Update Carousel

Power BI Monthly Update - December 2025

Check out the December 2025 Power BI Holiday Recap!

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.

View All