Forum Discussion

DangerHog's avatar
DangerHog
Frequent Visitor
8 years ago
Solved

create new table from begin date, end date, transactionuid

I'm not sure if this is in the correct Location...   I have a table of reservation data with a column for "reservation #", "begin date", "end date", and "revenue" (plus some other misc. columns).  ...
Need Help
ChrisMendoza's avatar
ChrisMendoza
Resident Rockstar
8 years ago

DangerHog,

 

  1. I have not taken online courses however, I have watched quite a few videos on https://www.sqlbi.com/, read a lot on https://www.daxpatterns.com/ and of course https://msdn.microsoft.com/en-us/query-bi/dax/dax-function-reference
  2. I've used a technique in Power Query the Query Editor in Power BI which may help you get started.

Using List.Transform:

 

List.Transform({Number.From([BeginDate])..Number.From([EndDate])},each Date.From(_))

 

Starting from a simple table in the Power Query Editor:

 

Add Column:

 

 

Expand the Custom Column:

 

 

Gets you:

 

 

Remove [BeginDate] & [EndDate] will get you to what I believe is your desired outcome (to start in you journey):

 

 

 

 

 

 

 

 

LindaK's avatar
LindaK
Frequent Visitor
5 years ago

Hi Chris,  
is there a way, how i can get these lines, but only on level year and month? I do not need lines on daily. 
Thank you! 

 

  • ChrisMendoza's avatar
    ChrisMendoza
    Resident Rockstar
    5 years ago

    LindaK - I suppose you could try

    List.Transform({Number.From([BeginDate])..Number.From([EndDate])},each Date.Month(_))
or
List.Transform({Number.From([BeginDate])..Number.From([EndDate])},each Date.Year(_))

     

    • LindaK's avatar
      LindaK
      Frequent Visitor
      5 years ago

      Thank you for such a quick reply, I tried this Date.Year or Date. Moth but it ended in error.  

      • ChrisMendoza's avatar
        ChrisMendoza
        Resident Rockstar
        5 years ago

        And that error is?