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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Richard_Halsall
Helper III
Helper III

Fill missing dates in rows based on value from another table

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

1 ACCEPTED SOLUTION
dufoq3
Super User
Super User

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

2 REPLIES 2
dufoq3
Super User
Super User

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.

Many thanks worked perfectly

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors