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"

ITIL 4 coming soon

More info on https://www.sysaid.com/blog/entry/whats-coming-in-itil-4.

Quoting blog post’s summary:

  • ITIL 4 is coming soon – early 2019.
  • It is an evolution of ITIL, not a revolutionary replacement.
  • It builds on many of the ideas in ITIL Practitioner.
  • It focusses more on how things fit together to create value, and less on process steps.
  • It’s going to be released in stages, with Foundation material available early in 2019.

How to get and view WordPress Statistics in Power BI

Soheil Bakhshi from http://biinsight.com has a super good article on how to get your stats from a WordPress site/blog and analyze them in Power BI.

For lazy guys out there, he already built a Power BI template file that you can use straight away. The only thing you’ll need is WordPress API key but don’t worry, he explains how to get that one too!

Check it out: http://biinsight.com/analyse-your-wordpress-blog-stats-in-power-bi/.

Expressions that yield variant data-type cannot be used to define calculated columns.

Error:

Expressions that yield variant data-type cannot be used to define calculated columns.

Cause:

IF based Calculated Columns in Power BI or Power Pivot with 2 different data types is not allowed.

Solution:

Format one data type as needed using FORMAT() function.


Age = IF (
TableName[Birthday] < TODAY(),
FORMAT (
Year ( TODAY() )- Year ( TableName[Birthday] ),"General Number" ),
"Invalid birth date"
)

FORMAT Function (DAX): https://msdn.microsoft.com/en-us/query-bi/dax/format-function-dax

Pre-Defined Numeric Formats for the FORMAT function: https://msdn.microsoft.com/query-bi/dax/pre-defined-numeric-formats-for-the-format-function

HTML vs XHTML

Ever wondered what HTML and XHTML is about? Long and official story can be found at W3Schools. Short story, below.

Simply put, XHTML is the same as HTML but stricter. There are rules in place that one must follow. You are no longer allowed to leave tags unclosed or place them wherever you want. And guess what, the only beneficiary from following these rules, is you, the web developer. Because your site will then be supported by most of the browsers out there; definitely more than your badly formatted HTML is today.

Quoting W3Schools, the most important differences from HTML are listed below.
Please visit W3Schools post to see examples of good vs bad code.

Document Structure

  • XHTML DOCTYPE is mandatory
  • The xmlns attribute in <html> is mandatory
  • <html>, <head>, <title>, and <body> are mandatory

XHTML Elements

  • XHTML elements must be properly nested
  • XHTML elements must always be closed
  • XHTML elements must be in lowercase
  • XHTML documents must have one root element

XHTML Attributes

  • Attribute names must be in lower case
  • Attribute values must be quoted
  • Attribute minimization is forbidden

How to convert from HTML to XHTML

  1. Add an XHTML <!DOCTYPE> to the first line of every page
  2. Add an xmlns attribute to the html element of every page
  3. Change all element names to lowercase
  4. Close all empty elements
  5. Change all attribute names to lowercase
  6. Quote all attribute values

Moreover, these guys also developed a website where you can validate your page to be XHTML (and not only) compliant. They will also tell you exactly what you did wrong and how to correct. And don’t worry, it’s free! It only costs your willingness to do it the right way.

Ok, e-books

Let’s start with some bullshit talk to tell you how this topic started. Feel free to skip it if you’re not curious.
Personally, I’m not the kind of person who reads books not related to IT; think I can count all books I read throughout my lifetime and there are no more than 10, but this is not something to be proud of. Anyway, since all my books are IT related and I mainly read them in front of a computer, after having some sleep issues last year, I decided I need to read something else before going to bed. Well, before falling asleep… So, I bought a Kindle e-book reader, yey!

Next issue, what a hell to read?! My wife only reads crime novels, so she couldn’t help in this matter. Although I thought about reading what she reads, mainly to see if she’ll prepare me something to eat from a crime book we’re good though, just had our 9th wedding anniversary!
I remember that when I was a kid I read something from Jules Verne, in Russian, and I liked it… Searching the web for some e-books I found a very nice webstore (www.bestseller.md) from Moldova that offers lots of free e-books. I downloaded a few and started reading in bed. That’s what I started with, Jules verne. It was so boring, I started falling asleep within minutes; mission accomplished!
After few chapters things started to get interesting in the book so I was reading more and more each day. I finished the book and I was proud of my fantastic achievement, lol.

I started the second book, also from Jules Verne, downloaded from same website (thanks, guys!). Here things started moving from reading side to IT side… mainly because first book I read had a nice handy feature that let me know how many minutes I have from current chapter or book. The second one, didn’t. It only said how much I have till end of the book. Well, well, well… why, what’s wrong?! I started digging… and this means I was no longer reading before going to bed, instead I was web-ing over this issue. Apparently, this second .mobi file was not formatted properly, specifically its chapters. Hmm…

Reading more and more about e-books, and how they’re created and published, seemed interestingly so I started testing how this works. I found no real step by step guide on how to do it. Lot’s of general articles covering this on a high level with steps to follow, blah, blah and in the end asking you to buy their e-books where they explain everything step by step. Well, gracias, what can I say.

Then I stumbled upon one publisher’s website with some info online (posted way back in 2013) and the rest of it in the e-book sold on Amazon (updated in January 2018). It was cheap, so I bought it. Of course, my eagle eyes immediately found many typos and errors in the book, that I emailed author about but… anyway, let’s get back to the point. Overall, that book is enough to get you started. Since I’m an IT guy, it was easy for me to understand everything, especially HTML and CSS things. Reading that book, I understood that this is what an e-book is all about – HTML, CSS and some XML. In fact, did you know that if you take an .epub file and change the extension to .zip, you will have an archive with everything in it (css file, html file, table of contents etc.)!? When I found out about it, I felt just like I did last decade – holy cow! This was when Microsoft first introduced their new XML based files (docx, xlsx etc.), and someone showed in a training that if you rename .docx to .zip, you get a nice structured zip file. technology, I love it!
Enough talking, let’s get started. Head of over to my next post, Creating an e-book. Part 1.

Getting back online

After many years, giving this blog a rebirth.

I lost my backup files and only found very few posts and those mainly about ConfigMgr 2012. Will post them below anyway; better few than nothing.

Next planned articles will be about e-books and plain html maybe, we shall see Smile Stay tuned!

Also need to create/update “About” page. Things have happened, so to speak… jobs changed, places changed etc.

I’ve been at Tech-Ed today…

Actually I just attended a local event organized by Microsoft Romania and the last session was Useful Hacker Techniques: Which Part of Hackers’ Knowledge Will Help You in Efficient IT Administration held by polish Enterprise Security MVP – Paula Januszkiewicz.

Geez… this girl presented some things that, I can bet, made half of all admins go home/work and revise their server’s security and configuration.

After seeing this presentation and demos, I will repeat what I said a couple of years ago: Romanian MVP’s still have to learn from the foreign ones. A lot!