Forum Discussion

MJC1's avatar
MJC1
Frequent Visitor
9 years ago

How to Include "Time" in Date Hierarchy

Hi Power BI wizards,   I am new to Power BI and I am trying to build some reports based on energy data from smart meters.   The data is pretty much a time series with a timestamp of the following ...
Need Help
OpenDataLab's avatar
OpenDataLab
Icon for Helper II rankHelper II
9 years ago

From a design point of view I would have gone with 2 seperate tables, a date table and a seperate time table.  

 

You will end up with the same result from a reporting point of view and your data model will be smaller and simplier.

MJC1's avatar
MJC1
Frequent Visitor
9 years ago

Hi OpenDataLab,

 

I like your idea very much.

I would like to try your approach out.

Do you have the sript of for the Date Table and for the Time Table that can be shared?

I look forward to hearing from you.

 

Cheers,

MJC

 

 

    • MJC1's avatar
      MJC1
      Frequent Visitor
      9 years ago

      Hi OpenDataLab,

       

      I am now trying this option but I am having some problems.

      I can load both power queries as per your script below. (just FYI I select the dates from 01/01/2017 to 31/12/2017)

       

      Now I am having problems when I load my date. Essentially my CSV file has two columns. Column 1 Header is "Date" and contains dates of the following format 01/01/2017 and Column 2 Header is "XYZ" and contains numbers as this is my dataset. My data set starts at 01/01/2017 and finishes 24/07/2017 and moves on a 30 min time step.  When I try to load my get i get a message sayin that there are problems with rows. Perhaps this has to do with the Parse thing you mentioned. I'd be greatful if you could clarify how I could solve this or consider the parse. I need a little more info than that on your figure. I am a real novice.

       

      Could it also be because the dates on my data are for half year whereas the range i selected in the power query is for entire year.

      Many thanks for your help in advance.

      • OpenDataLab's avatar
        OpenDataLab
        Icon for Helper II rankHelper II
        9 years ago

        Try this.  

         

        The other way to parse the data is to change the data type, but when you do this you need to specifiy the locale of the source data.  One way to do this is to choose the "Using locale.." option on the change data type drop down of the column.

         

         

        Once you select this option you will be presented with the following screen.  Choose the options illustrated below.

         

        Now Power Query nows what the format of your source data, it can interpret the data correctly and convert it.