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"

Power BI Data Profiling – distinct vs unique

“Another month, another Power BI Desktop update” as Guy(s) in a Cube use to say.
Power BI Desktop October 2018 update brings in quite a few updates, with one thing being my favorite as I always do/check it manually when working with new data: Data Profiling. For more info and how to enable it, visit Microsoft’s official Power BI blog post or the detailed post from Matt Allington on https://exceleratorbi.com.au.

Ok, I don’t want to make it a long post, so here’s the thing: one of the Data Profiling feature is “Column distribution”.

Column Distribution allows you to get a sense for the overall distribution of values within a column in your data previews, including the count of distinct values (total number of different values found in a given column) and unique values (total number of values that only appear once in a given column).

So, distinct and/or unique. For anyone else these 2 words may seem similar, but for Data Guys like us, these are 2 different things (just like empty and null 🙂 ). Still confused? Here’s a very simple example to clear things up:

Let’s say we have a small company with 10 employees using 10 laptops from different Manufacturers: HP, Dell, Apple and Lenovo.
Manufacturers in Power BI's Column distribution
Distinct: We have laptops from 4 different Manufacturers (HP, Dell, Apple and Lenovo) aka “total number of different values”, regardless of how many of each we have.
Unique: We have only 2 laptops that nobody else have in our company (one from Apple and another one from Lenovo) aka “total number of values that only appear once”.

Thinking legacy, here’s the same thing in Excel, using countif or countifs.
Excel CountIF formula

Power BI: The key didn’t match any rows in the table

Error:

Refreshing Power BI report generates “The key didn’t match any rows in the table” error.

Cause:

Click “Edit Queries” button. If you do not see the error message, click “Refresh Preview” button. Once you have the error message, click “Go To Error“.

Error message will remain, but you should have “Edit Settings” button now, click it.

In the next screen, Navigation, you should see what exactly Power BI is trying to access but cannot do so.

In my example, I have an Excel file as my source. When I first connected to this data source, my Table name was Table1. Yesterday I changed where my Excel file gets data from and this, in turn, changed Excel’s Table name to “report“.

Obviously, when Power BI tries to refresh the data, it cannot find Table1 table anymore.

Solution:

In the same Navigation screen, selecting my new Table name “report” will fix the issue assuming all other columns in the Excel file are the same.

Same is true for any data source, not only Excel file. Follow same steps to identify what’s causing the error and then fix it as needed.

How to automatically create and update Visio diagrams from Excel

To successfully create Visio diagrams from Excel, Visio Pro for Office 365 is required. Here you can see the prices or download trial.

  1. Open Excel (I have latest release of Excel from Office 365 suite)
  2. In the search box, type “process” and hit Enter. Among other search results, 2 Excel templates should appear with Excel icons in upper left corner and Visio diagrams in lower right corner. Select the one you want/need. I will continue with Process Map for Cross-Functional Flowchart.

  3. A nicely formatted Excel file will open with pretty much all info you need to know about Data Visualizer; the solution that allows us to create diagrams from Excel.

  4. Head over to Process Map worksheet.
  5. You’ll notice here we have “ProcessMapDataTable populated with 2 rows. This is the Excel Table that we need to work with. Everything else can be deleted/ignored.

  6. Remove the 2 rows added as examples and add your process’ field mappings. For this example, I will use Software Development process found on www.smartdraw.com
  7. I will modify my Excel Table based on this process. Here is my final Table:
Process Step ID Process Step Description Next Step ID Connector Label Shape Type Function Phase
P01 Start P02 Start Project Management Planning
P02 Gather requirements P03 Document Project Management Planning
P03 Develop specifications P04 Process Software Design Design
P04 Develop internal design P05 Process Software Design Design
P05 Develop external design P06 Process Software Design Design
P06 Change required? P05,P07 Yes,No Decision Software Design Design
P07 Define Development team P08 Subprocess Software Development Coding
P08 Application development P09 Process Software Development Coding
P09 User Acceptance Testing P10 Process Software Development Coding
P10 Quality Assurance P11 Process Software Development Coding
P11 Defects P08,P12 Yes,No Decision Software Development Coding
P12 Release to production P13 Subprocess Release Management Release
P13 Stop End Project Management Release

Make sure that you have more than one value in Next Step ID, you should have similar number of values/labels in Connector Label column and that those values match its corresponding step.

For example, P06 Step ID is a decision point. If changes are required (Yes), process goes back to P05 Step ID. If no changes are required (No), process proceeds to P07 Step ID.

Lastly, ensure your last process step has nothing in Next Step ID field, because it’s the last one, there is nothing else afterwards… process ends here. Save your Excel file.

  1. Open Visio.
  2. In the search box, type “data visualizer” and hit Enter. You should get the Basic and Cross-Functional templates. Select the same one you used for creating the Excel file. Cross-Functional Flowchart in my example.

    Just in case you started it wrongly or you want to get Excel template again, click Excel data template. Select your local Unit and click Create.

  3. Visio will open Create Diagram from Data wizard.
  4. Using first drop-down box, select the correct diagram type to use.
  5. In the second field, browse to the Excel file you created and saved in Step 7.
  6. In the last field, Visio should automatically recognize your Excel Table name, ProcessMapData in my example. Click Next.

  7. If you did not modified headers in the Excel Table, next page should automatically recognize and map Function and Phase columns. Expand More Options if you want to modify the order of column values. Assuming you created your process in a chronological order, you’d probably want to retain the same order in Visio.

  8. On the next wizard page the same, if you left all default column headers, Visio will automatically recognize and map needed columns. Otherwise, drag and drop the columns from the left.

    Short note here: you probably noticed your Excel Table has blue and green headers. Blue headers are required for these mappings and building your final diagram. Whether green columns are nice to have. All those columns may have valuable info for the diagram, but I think Microsoft still has ways for improvement this specific side, especially Alt Description, because it may contain too much text. Add something to this column and you will see what I’m referring to.

  9. Next page is about choosing your required Visio shapes. I did not have anything to modify or comment here. It seems like Microsoft is limiting the user to use predefined shapes but, anyway, default shapes are kind of standard so should be sufficient.

  10. The last wizard step is the one we need to modify to suit our needs. Make sure Next Step ID is selected for Specify Column Name, Relationship is set to Next Step, Delimiter is set to comma and Connector Label is mapped to its column with same name. Click Finish.

  11. You should now have a fully functional Visio diagram. Note that Visio will zoom it in such a way that it fits your page; if needed, zoom it in or out to see the details. Also, for some reason, Visio creates a lot of unused space between each shape. Feel free to rearrange everything to save space.

One nice thing about having this Excel file linked to Visio is that any updates you make to the process in Excel, you can refresh Visio diagram to update it with those changes. Just select your Visio diagram and from Data tab, in Create from Data group select Refresh.

For reading official Microsoft article about this, please take a look at “Create a Data Visualizer diagram” post. It contains more valuable info, including a description about each Excel columns that interact with Visio flowchart components.

Similarly, here’s a video made by Microsoft.

July 20, 2018: Microsoft just posted another, shorter, video on YouTube marketing same thing.