Forum Discussion

ChenChristyYu's avatar
ChenChristyYu
Helper II
8 years ago
Solved

x-axis timeline

Hi experts,

 

I am new to PowerBI.  am trying to use the timeline as an x-axis in stacked area chart, and count how many projects are being done every minute. How should I set up the timeline down to minutes within a certain period of time (e.g.: from January 1st to the end of January)? 

 

THANKS a lot.

 

Best,

Chen

 

Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    8 years ago

    Hi ChenChristyYu,

     

    You can create a calendar table using Power Query (New Source -> Blank Query). Then, create a relationship between this calendar table and your source table based on date/time field.

    let
    Source = Table.FromColumns({{Number.From(#date(2018,1,1))..Number.From(#date(2018,1,31))}}),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.Times(#time(0, 0, 0), 48, #duration(0, 0, 30, 0))),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom")
in
    #"Expanded Custom"

     

    Best regards,

    Yuliana Gu

2 Replies

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Microsoft Employee
    8 years ago

    Hi ChenChristyYu,

     

    You can create a calendar table using Power Query (New Source -> Blank Query). Then, create a relationship between this calendar table and your source table based on date/time field.

    let
    Source = Table.FromColumns({{Number.From(#date(2018,1,1))..Number.From(#date(2018,1,31))}}),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.Times(#time(0, 0, 0), 48, #duration(0, 0, 30, 0))),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom")
in
    #"Expanded Custom"

     

    Best regards,

    Yuliana Gu

    • ChenChristyYu's avatar
      ChenChristyYu
      Helper II
      8 years ago

      Hi Gu,

       

      Thanks for your reply. I am curious if I want to create a new table based on other queries in the same PowerBI project, what is the code format for the source? for example,  I want to create a new table called 'returns' selecting columns from table 'sales', how can I do it.

      Sources = ...?