Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Load autogenerated date table to query editor

Hi all,

I created a DimDate table where Power BI automatically generates the dates that I use within my data set and creates automatically the Year, Quarter, Month and Day (Cfr. Screenshot below). Now I would like to transform this auto-generated information through the edit queries section as I would like to add additional columns as well (such as the Book Year which differs from the current year).

 

E.g. BY 19  started from 1st of November 2018- 31st of October 2019. Therefore, I would like to create a column with the following logic:

A. Extract right bookyear

  • If month > 10 , then (current year -1) 
  • If month =<10, then, (current year)

B. Keep last 2 numbers

  • Extract last 2 characters (So I go from 2019 -> 19)

C. Add BJ before 19

  • Create new column BJ and merge it with 19

Any ideas on how to load this table to the query editor?

 

Thanks in advance!

 

DimTime

Need Help

2 Replies

  • sturlaws's avatar
    sturlaws
    Icon for Resident Rockstar rankResident Rockstar
    6 years ago

    Hi Anonymous 

     

    if you by "edit queries" mean the Power Query, you cannot edit calculated tables like your dimTime in that editor.

    But what you are asking can be done with calculated columns:

    BY =
VAR _y =
    IF (
        'Table 2'[Date].[MonthNo] > 10;
        'Table 2'[Date].[Year] + 1;
        'Table 2'[Date].[Year]
    )
RETURN
    CONCATENATE ( "BY "; RIGHT ( _y; 2 ) )

     

    Cheers,
    Sturla

    If this post helps, then please consider Accepting it as the solution. Kudos are nice too.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    HI Anonymous ,

    You can consider right-clicking on that table to copy table records, then paste copied data to an external file(e.g. excel) or direct use 'enter data' feature to enter copied data.

    In addition, you can also create a calendar table with custom columns on the query editor side:

    Create a Custom Calendar in Power Query 
    Regards,

    Xiaoxin Sheng