Excel Forum

Count Zero between Startdate and EndDate

Chris Yap

Member

Members

Forum Posts: 162

Member Since:
August 21, 2019

Offline

March 15, 2020 - 11:59 am

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

Catalin Bombea

Iasi, Romania

Member

Dashboards

Power Query

Power Pivot

Xtreme Pivot Tables

Excel for Decision Making

Excel for Finance

Excel Analysis Toolpak

Power BI

Excel

Word

Outlook

Excel Expert

Excel Customer Service

PowerPoint

Forum Posts: 1939

Member Since:
November 8, 2013

Offline

March 15, 2020 - 2:29 pm

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?

Chris Yap

Member

Members

Forum Posts: 162

Member Since:
August 21, 2019

Offline

March 15, 2020 - 5:44 pm

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 !

Catalin Bombea

Iasi, Romania

Member

Forum Posts: 1939

Member Since:
November 8, 2013

Offline

March 16, 2020 - 5:05 pm

Try this query (remove last column from the existing table):

let
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"

Chris Yap

Member

Members

Forum Posts: 162

Member Since:
August 21, 2019

Offline

March 16, 2020 - 6:07 pm

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 !

Catalin Bombea

Iasi, Romania

Member

Forum Posts: 1939

Member Since:
November 8, 2013

Offline

March 17, 2020 - 1:16 am

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.

Chris Yap

Member

Members

Forum Posts: 162

Member Since:
August 21, 2019

Offline

March 19, 2020 - 4:49 pm

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

Catalin Bombea

Iasi, Romania

Member

Forum Posts: 1939

Member Since:
November 8, 2013

Offline

March 21, 2020 - 3:35 pm

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