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.
Important previous steps there is a 'Custom group by' that produces an index column with 1 being the latest leave date, 2 being second latest etc. The data has also been filtered to just 1 Personnel Number in this example, this problem occurs regardless of how many or what Personnel Number
In this table 'Emp leaves latest' you can see one row of information, the Filtered Rows1 step is where Index column created by a 'Custom Group by' has been filtered to = 1. As you can see below the only Leave Type remaining here is "layoff'. I am looking to Merge this table in to another. 'Injury/Illness' was another Leave Type but was filtered out as the Index in that row = 2
In the picture below you are able to see that this table 'Workers' has only one row, I have made sure that the Personnel Number is the same in both tables of course.
Please see the merge below. You are able to see in the preview that the only Leave Type available is 'Layoff'
The Picture below shows us the step once the newly merged table has been expanded, you can see that the Leave Type that has come through is 'Injury/ Illness'. This shouldn't be
Any ideas or help would be appreicted.
Trystan
Solved! Go to Solution.
The cause of your issue is a known phenomenon: if you sort a table and then use Table.GroupBy or Table.Distinct with the sorted table as source, the sort order is not necessarily maintained.
In your case, it means that the nested tables can have a different sort order then the original table.
Apparently you can even have different results in different queries.
The solution is to buffer the sorted table:
#"Sorted Rows" = Table.Buffer(Table.Sort(#"Replaced Values1",{{"HcmWorker_FK_PersonnelNumber", Order.Ascending},{"StartDateLeave", Order.Descending},{"EndDate", Order.Descending}})),
Hi @Tcoe12
Is it possible to share the raw data of employees leave latest in excel format and share the link here for finding a solution.
Also can you paste the query using Advanced editor for the employees leaves latest step, to check if there is any thing is missing.
Cheers
CheenuSing
Hi CheenuSing,
Many thanks for your response and offer of assistance.
Here is the link to the Excel data type https://1drv.ms/x/s!Aq-6rFtHe6YQaBvE3ECQ3h2nZ2Q
Here is the Advanced Editor below for the whole of Employees Leaves Latest (this advanced editor is working with Desktop excel workbook) and below the Employee Leaves Latest is the code for the 'Workers' table
Employee Leaves Latest let Source = Excel.Workbook(File.Contents("C:\Users\tcoe\Documents\Emp Masters Issue.xlsx"), null, true), #"Emp Leaves Latest_Sheet" = Source{[Item="Emp Leaves Latest",Kind="Sheet"]}[Data], #"Promoted Headers" = Table.PromoteHeaders(#"Emp Leaves Latest_Sheet", [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"HcmLeaveType_FK_LeaveTypeID", type text}, {"HcmWorker_FK_PersonnelNumber", type text}, {"Note", type text}, {"CompanyInfo_FK_DataArea", Int64.Type}, {"HcmEmployment_FK_ValidFrom", type datetime}, {"StartDateLeave", type datetime}, {"CompanyInfo_FK_PartyNumber", Int64.Type}, {"EndDate", type datetime}, {"HcmEmployment_FK_ValidTo", type datetime}}), #"Changed Type1" = Table.TransformColumnTypes(#"Changed Type",{{"EndDate", type date}}), #"Added Custom" = Table.AddColumn(#"Changed Type1", "Leave Active YN", each if ([EndDate] > DateTime.Date(DateTime.LocalNow())) then ("Y") else ("N")), #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Leave Active YN] = "Y")), #"Replaced Values" = Table.ReplaceValue(#"Filtered Rows",#date(2154, 12, 31),null,Replacer.ReplaceValue,{"EndDate"}), #"Replaced Values1" = Table.ReplaceValue(#"Replaced Values",#date(2063, 12, 31),null,Replacer.ReplaceValue,{"EndDate"}), #"Sorted Rows" = Table.Sort(#"Replaced Values1",{{"HcmWorker_FK_PersonnelNumber", Order.Ascending},{"StartDateLeave", Order.Descending},{"EndDate", Order.Descending}}), #"Custom Grouping" = Table.Group(#"Sorted Rows" , {"HcmWorker_FK_PersonnelNumber"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}), #"Expanded Partition" = Table.ExpandTableColumn(#"Custom Grouping", "Partition", {"HcmLeaveType_FK_LeaveTypeID", "CompanyInfo_FK_DataArea", "HcmEmployment_FK_ValidFrom", "StartDateLeave", "CompanyInfo_FK_PartyNumber", "EndDate", "HcmEmployment_FK_ValidTo", "Index"}, {"HcmLeaveType_FK_LeaveTypeID", "CompanyInfo_FK_DataArea", "HcmEmployment_FK_ValidFrom", "StartDateLeave", "CompanyInfo_FK_PartyNumber", "EndDate", "HcmEmployment_FK_ValidTo", "Index"}), #"Added Custom1" = Table.AddColumn(#"Expanded Partition", "New ID", each "AB"), #"Sorted Rows1" = Table.Sort(#"Added Custom1",{{"Index", Order.Ascending}}), #"Filtered Rows1" = Table.SelectRows(#"Sorted Rows1", each ([Index] = 1)), #"Added Custom2" = Table.AddColumn(#"Filtered Rows1", "Index letters", each if [Index] = 1 then "A" else if [Index] = 2 then "B" else ""), #"Removed Other Columns" = Table.SelectColumns(#"Added Custom2",{"HcmWorker_FK_PersonnelNumber", "HcmLeaveType_FK_LeaveTypeID"}) in #"Removed Other Columns" Workers Code let Source = Excel.Workbook(File.Contents("C:\Users\tcoe\Documents\Emp Masters Issue.xlsx"), null, true), Workers_Sheet = Source{[Item="Workers",Kind="Sheet"]}[Data], #"Promoted Headers" = Table.PromoteHeaders(Workers_Sheet, [PromoteAllScalars=true]), #"Added Custom" = Table.AddColumn(#"Promoted Headers", "New ID", each "AB"), #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"PersonnelNumber"}), #"Merged Queries" = Table.NestedJoin(#"Removed Other Columns",{"PersonnelNumber"},#"Emp Leaves Latest",{"HcmWorker_FK_PersonnelNumber"},"Query1",JoinKind.LeftOuter), #"Expanded Query1" = Table.ExpandTableColumn(#"Merged Queries", "Query1", {"HcmLeaveType_FK_LeaveTypeID"}, {"Query1.HcmLeaveType_FK_LeaveTypeID"}) in #"Expanded Query1"
Hi @Tcoe,
I am unable to download the excel file. Can you upload it on google drive and share the link.
Cheers
CheenuSing
Hi CheenuSing,
Apologies for the delay in getting you this revised link!
Please find it here
https://drive.google.com/open?id=0B5coafxgNnpFNmktWXZFeXdnU2M
Any further questions please don't hesitate to ask.
Many Thanks
Trystan
The cause of your issue is a known phenomenon: if you sort a table and then use Table.GroupBy or Table.Distinct with the sorted table as source, the sort order is not necessarily maintained.
In your case, it means that the nested tables can have a different sort order then the original table.
Apparently you can even have different results in different queries.
The solution is to buffer the sorted table:
#"Sorted Rows" = Table.Buffer(Table.Sort(#"Replaced Values1",{{"HcmWorker_FK_PersonnelNumber", Order.Ascending},{"StartDateLeave", Order.Descending},{"EndDate", Order.Descending}})),
I really appreciate(you both) you taking your time to help me with this issue, in particular the work around that you have provided.
Hopefully MS can straighten out this 'known phenomenon',
Thanks again
Tcoe
Hi CheenuSing,
Many thanks for your response and offer of assistance.
I have attached the Excel file, and have pasted the Advanced Editor below for the whole of Employees Leave Latest
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
84 | |
67 | |
60 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |