Hi,
What is the shortest and effective way to do in PowerQuery ? I did extract out the day of start and end, and attempting to compare whether it is greater or less than, then countifs in native excel, haven't really figure out the formulae
Can use M codes in Power Query to achieve it ?
Thank you in advance
Hi Chris,
Please prepare a clear example of what you are trying to achieve. Looking at your example does not clarify the problem, the first row shows a count of 4, and you have 6 zero values in columns 1-14 (5 if we start from day 4). I guess the columns represents days from the same month as the start /end dates? Can the end date be in a different month than start date?
Hi Catalin
Sorry my mistake
Yes, for Staff A, only take into account 4/3/20 to 21/3/20 all the Zero, so the return should be 6 (there are 6 more days to count), Staff B should be 5 so far (there are 4 more days)
End date can be across till next month
Basically we want to monitor the number zeros a person have in a specific range of dates
Thank you !
Try this query (remove last column from the existing table):
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Extracted Day" = Table.TransformColumns(Source,{{"Start date", Date.Day, Int64.Type}, {"End date", Date.Day, Int64.Type}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Extracted Day", {"Name", "Start date", "End date"}, "Attribute", "Value"),
#"Changed Type1" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"Attribute", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom", each [Attribute]>=[Start date] and [Attribute]<=[End date]),
#"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = true) and ([Value] = 0)),
#"Grouped Rows" = Table.Group(#"Filtered Rows", {"Name"}, {{"Count", each Table.RowCount(_), type number}}),
#"Merged Queries" = Table.NestedJoin(Source, {"Name"}, #"Grouped Rows", {"Name"}, "Grouped Rows", JoinKind.LeftOuter),
#"Expanded Grouped Rows" = Table.ExpandTableColumn(#"Merged Queries", "Grouped Rows", {"Count"}, {"Count"})
in
#"Expanded Grouped Rows"
Hi Catalin
Thank you
it works perfectly if all days within a month
can you enlighten me what if the days cross over to next month, what is the tweaking to your code ?
Thank you !
Hi Chris,
Already asked "Can the end date be in a different month than start date?", but did not saw a clear answer for this question, so I assumed based on your structure that all columns will refer to current month.
My guess is that the columns structure is confusing, how will they look with data from 2 months? You have now numbers from 1 to 15 as column names, I doubt that you can add 2 months as days in columns, how will they look like? 1...31, then another set of columns with 1..30 numbers? You cannot have duplicates in column names...
Please provide a structure that matches your description, with columns spanning 2 months.
As you see in the query provided, we keep only Name and Start/end date columns, all other columns will be un-pivoted.
Those columns should at least have the month number, not just the day, not to mention that it can be in two years as well, if one month is december and the next is january next year.
The full date is best to use as column names, just remove the step that extracts the day from Start/end dates and simply compare the Attribute column (that should have now full dates) with start/end dates, in #"Added Column" step: each [Attribute]>=[Start date] and [Attribute]<=[End date]
Attribute column should be formatted ad Date.
Apologise Sir, forgotton to insert the mock source to you, by the way, manage to develop the query and see whether you have more efficient way, or that is the best already
Note :- attached revised, tab 1 source is the flat table, tab 3 is what I developed and load to excel, tab 2 is the desired result
Glad to hear you managed to make it work.
By the way, the last file source data is completely different to the initial file provided, I thought that was the initial data structure.
Next time, please try to clarify the problem from the beginning, by showing the original structure and the desired structure, this one was misleading.
Cheers,
Catalin
