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: 

A new Data Days event is coming soon! This time we’re going bigger than ever. Fabric, Power BI, SQL, AI and more. Don't miss out.

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
586 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
566 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
567 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
556 Views

Helpful resources

Announcements
May Power BI Update Carousel

Power BI Monthly Update - May 2026

Check out the May 2026 Power BI update to learn about new features.

Fabric SQL PBI Data Days

Data Days 2026 coming soon!

Sign up to receive a private message when registration opens and key events begin.

New to Fabric survey Carousel

New to Fabric Survey

If you have recently started exploring Fabric, we'd love to hear how it's going. Your feedback can help with product improvements.

Power BI DataViz World Championships carousel

Power BI DataViz World Championships - June 2026

A new Power BI DataViz World Championship is coming this June! Don't miss out on submitting your entry.

View All