Power Query – Week start and end dates in same column

Someone asked me the other day below thing: “On the same column I want a Range of dates not Duration between two dates So I have a Creation date and fulfillment date I need to come up with a column of weekly Range like for example
10/21 – 10/27
10/28 – 11/01
11/02 – 11/09
Something like this.

The solution can be pretty simple and mostly done through Power Query’s GUI.
Add a date range first using either way you want.
Then under Add Column – Date, you can easy add “Start of Week” and “End of Week”.

Note that if your week starts on Sunday, you don’t need to provide the first day of the week. I need my week to start on Monday so I added 1, instead of 0 or nothing “Date.EndOfWeek([Date],1)“.

The only thing to do now is to merge these two columns and put a separator of your choice. This will give you a column with Week’s start and end dates in the long format.

ex1

If you want month and date (or only date) you need to extract month and then date separately from both your Start and End of Week columns. Then merge month and date from Start of the week and again from End of the week.

At this moment, you only need to merge these 2 last columns, remove all unneeded columns and that’s it.

short

Here’s the entire query I ended up with.


let
Query1 = let
Source = #date(2019,1,1),
Custom1 = List.Dates(Source, Number.From(DateTime.LocalNow())- Number.From(Source) ,#duration(1,0,0,0)),
#"Converted to Table" = Table.FromList(Custom1, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type date}})
in
#"Converted to Table",
#"Changed Type" = Table.TransformColumnTypes(Query1,{{"Column1", type date}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "Date"}}),
#"Sorted Rows" = Table.Sort(#"Renamed Columns",{{"Date", Order.Descending}}),
#"Inserted Start of Week" = Table.AddColumn(#"Sorted Rows", "Start of Week", each Date.StartOfWeek([Date],1), type date),
#"Inserted End of Week" = Table.AddColumn(#"Inserted Start of Week", "End of Week", each Date.EndOfWeek([Date],1), type date),
#"Inserted Merged Column" = Table.AddColumn(#"Inserted End of Week", "Long range", each Text.Combine({Text.From([Start of Week], "en-US"), Text.From([End of Week], "en-US")}, " - "), type text),
#"Inserted Month" = Table.AddColumn(#"Inserted Merged Column", "Month", each Date.Month([Start of Week]), Int64.Type),
#"Inserted Day" = Table.AddColumn(#"Inserted Month", "Day", each Date.Day([Start of Week]), Int64.Type),
#"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Inserted Day", {{"Month", type text}, {"Day", type text}}, "en-US"),{"Month", "Day"},Combiner.CombineTextByDelimiter("/", QuoteStyle.None),"Start"),
#"Inserted Month1" = Table.AddColumn(#"Merged Columns", "Month", each Date.Month([End of Week]), Int64.Type),
#"Inserted Day1" = Table.AddColumn(#"Inserted Month1", "Day", each Date.Day([End of Week]), Int64.Type),
#"Merged Columns1" = Table.CombineColumns(Table.TransformColumnTypes(#"Inserted Day1", {{"Month", type text}, {"Day", type text}}, "en-US"),{"Month", "Day"},Combiner.CombineTextByDelimiter("/", QuoteStyle.None),"End"),
#"Merged Columns2" = Table.CombineColumns(#"Merged Columns1",{"Start", "End"},Combiner.CombineTextByDelimiter(" - ", QuoteStyle.None),"Short range"),
#"Removed Columns" = Table.RemoveColumns(#"Merged Columns2",{"Start of Week", "End of Week"})
in
#"Removed Columns"

Computers without Java 6 Update 30

select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier, SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System inner join SMS_G_System_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.Version < “6.0.300” and SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName like “Java(TM) 6%”

Collections with computers without Forefront Endpoint Protection 2010 client

A customer asked me today to help him with creating some collections with client computers that do not have FEP 2010 installed so he can advertise the antivirus client to those computers. You can find the queries bellow.

All Windows 7 without Forefront Endpoint Protection 2010

select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier, SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System inner join SMS_G_System_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_OPERATING_SYSTEM on SMS_G_System_OPERATING_SYSTEM.ResourceID = SMS_R_System.ResourceId where SMS_G_System_COMPUTER_SYSTEM.Name not in (select distinct SMS_G_System_COMPUTER_SYSTEM.Name from SMS_R_System inner join SMS_G_System_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = “Microsoft Forefront Endpoint Protection 2010”) and SMS_R_System.Client = 1 and SMS_G_System_OPERATING_SYSTEM.Caption like “%Microsoft Windows 7%”

All Windows Vista without Forefront Endpoint Protection 2010

select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier, SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System inner join SMS_G_System_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_OPERATING_SYSTEM on SMS_G_System_OPERATING_SYSTEM.ResourceID = SMS_R_System.ResourceId where SMS_G_System_COMPUTER_SYSTEM.Name not in (select distinct SMS_G_System_COMPUTER_SYSTEM.Name from SMS_R_System inner join SMS_G_System_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = “Microsoft Forefront Endpoint Protection 2010”) and SMS_R_System.Client = 1 and SMS_G_System_OPERATING_SYSTEM.Caption like “%Microsoft® Windows Vista%”

Windows XP Professional SP2 without Forefront Endpoint Protection 2010

select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier, SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System inner join SMS_G_System_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_OPERATING_SYSTEM on SMS_G_System_OPERATING_SYSTEM.ResourceID = SMS_R_System.ResourceId where SMS_G_System_COMPUTER_SYSTEM.Name not in (select distinct SMS_G_System_COMPUTER_SYSTEM.Name from SMS_R_System inner join SMS_G_System_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = “Microsoft Forefront Endpoint Protection 2010”) and SMS_R_System.Client = 1 and SMS_G_System_OPERATING_SYSTEM.CSDVersion like “%Service Pack 2%” and SMS_G_System_OPERATING_SYSTEM.Caption = “Microsoft Windows XP Professional”

Windows XP Professional SP3 without Forefront Endpoint Protection 2010

select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier, SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System inner join SMS_G_System_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_OPERATING_SYSTEM on SMS_G_System_OPERATING_SYSTEM.ResourceID = SMS_R_System.ResourceId where SMS_G_System_COMPUTER_SYSTEM.Name not in (select distinct SMS_G_System_COMPUTER_SYSTEM.Name from SMS_R_System inner join SMS_G_System_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = “Microsoft Forefront Endpoint Protection 2010”) and SMS_R_System.Client = 1 and SMS_G_System_OPERATING_SYSTEM.CSDVersion like “%Service Pack 3%” and SMS_G_System_OPERATING_SYSTEM.Caption = “Microsoft Windows XP Professional”

Collection with client Computers without ConfigMgr client

select SMS_R_SYSTEM.ResourceID, SMS_R_SYSTEM.ResourceType, SMS_R_SYSTEM.Name, SMS_R_SYSTEM.SMSUniqueIdentifier, SMS_R_SYSTEM.ResourceDomainORWorkgroup, SMS_R_SYSTEM.Client from SMS_R_System inner join SMS_G_System_OPERATING_SYSTEM on SMS_G_System_OPERATING_SYSTEM.ResourceId = SMS_R_System.ResourceId where SMS_R_System.Client = 0 and SMS_G_System_OPERATING_SYSTEM.Caption not like “%Server%” or SMS_R_System.Client is null  and SMS_G_System_OPERATING_SYSTEM.Caption not like “%Server%”

Collection based on computer model

select SMS_R_SYSTEM.ResourceID, SMS_R_SYSTEM.ResourceType, SMS_R_SYSTEM.Name, SMS_R_SYSTEM.SMSUniqueIdentifier, SMS_R_SYSTEM.ResourceDomainORWorkgroup, SMS_R_SYSTEM.Client from SMS_R_System inner join SMS_G_System_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId where SMS_G_System_COMPUTER_SYSTEM.Model like “%HP ProBook 4710s%”

Upgrade Configuration Manager client

Recently I reinstalled ConfigMgr server in my company and was looking for an easy method to upgrade all Configuration Manager clients to 4.00.6487.2000 version.
Put some notes on the table and finally made the following plan:

  • Create a report that counts all client versions. (This is optional, just for informational purposes).
    Report query is:SELECT TOP (100) PERCENT Client_Version0 AS [ConfigMgr client version], COUNT(Client_Version0) AS Total
    FROM dbo.v_R_System GROUP BY Client_Version0, Client0 HAVING (Client0 = 1)
    ORDER BY Total DESC, [ConfigMgr client version]
  • Create a collection (“Older Clients” for example) with all system resources with a client version not 4.00.6487.2000.
    Collection query is:SELECT SMS_R_SYSTEM.ResourceID, SMS_R_SYSTEM.ResourceType, SMS_R_SYSTEM.Name, SMS_R_SYSTEM.SMSUniqueIdentifier, SMS_R_SYSTEM.ResourceDomainORWorkgroup, SMS_R_SYSTEM.Client
    FROM SMS_R_System
    WHERE SMS_R_System.ClientVersion != "4.00.6487.2000"

    This way, system resources with older client version will be members of this collection.

  • Created a package and program with ConfigMgr client upgrade with custom command line parameters.
    Program command line is:CCMSETUP.EXE /noservice SMSSITECODE=CFM SMSCACHESIZE=1024 SMSSLP=BLAH.DOMAIN.COM SMSMP=BLAH.DOMAIN.COM RESETKEYINFORMATION=TRUE
  • Advertised it to “Older Clients” collection.

Now, as system resources with older client version are members of this collection they will receive the advertisement and will silently install the latest ConfigMgr client.

When the collection will have no system resources, I will know that all clients are upgraded. Also, I can check this by opening the same report from any browser on any computer.

WMI query for processor manufacturer

Following the post regarding how to install drivers based on computer model using WMI query, here is another query you can use in your task sequences to install some processor specific software for example.

To find the processor manufacturer during a task sequence, insert a condition for the step you need, and select Query WMI. Then, for WMI Namespace use root\cimv2. For WQL Query use SELECT * FROM Win32_Processor WHERE Manufacturer like “%GenuineIntel%” or SELECT * FROM Win32_Processor WHERE Manufacturer like “%AuthenticAMD%” depending on what you need.

To find the processor manufacturer of your computer, open a command prompt and type the following: WMIC CPU GET Manufacturer.