Forum Discussion

Xperl's avatar
Xperl
Regular Visitor
8 years ago
Solved

DATE Issue - PB Publisher for EXCEL

Hi,

 

I'm working on a POWER BI project to analyse ERP data connecting POWER BI to SQL.

 

For my accounting service i need to user POWER BI PUBLISHER for Excel.

 

But i've an issue with the date format in the PB Builder it's fine, Excel dont reconize the date format ... :

 

Date format is OK in the builder

 

Date format is not reconized in Publisher for Excel

 

 

Could you please help me ? Did i do something wrong ?

 

Thank's for your help !

Ben

Need Help
  • Xperl's avatar
    Xperl
    8 years ago

    Hi V-Ljerr,

     

    Thanks for your answer.

    I just try to update my Windows 10, Power BI Desktop and Power BI Publisher but still the same issue :( ... it look like a date format but it's not :

     

     

     

    I tried to connect POWER QUERY for Excel strait to my SQL Database to compare and the result is not the same ! I have date +year / month date automaticaly and i can use chronology function :

     

     

    To move forward i created a calendar dimension table in power bi with the following DAX expression and i linked it with my other table to filter data. It works fine ;) :

     

    CALENDRIER = ADDCOLUMNS (CALENDAR(DATE(2010;1;1); DATE(2020;12;31)); "Année"; YEAR ( [Date] ); "N° Mois"; FORMAT ( [Date]; "MM" ); "Mois"; FORMAT ( [Date]; "mmm" ); "N°Semaine"; WEEKDAY ( [Date] ); "Jour"; FORMAT ( [Date]; "dddd" ); "Trimestre"; "T" & FORMAT ( [Date]; "Q" ))

    Thanks for your help !

    Ben

     

     

     

     

     

     

     

     

2 Replies

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    Hi Xperl,

     

    I just verified that the date field can be recognized correctly by Excel with the latest version of Power BI publisher for Excel in my environment.

     

     

    Could you try downloading and installing the latest version of Power BI publisher for Excel from here to see if it works? :smileyhappy:

     

    Regards

    • Xperl's avatar
      Xperl
      Regular Visitor
      8 years ago

      Hi V-Ljerr,

       

      Thanks for your answer.

      I just try to update my Windows 10, Power BI Desktop and Power BI Publisher but still the same issue :( ... it look like a date format but it's not :

       

       

       

      I tried to connect POWER QUERY for Excel strait to my SQL Database to compare and the result is not the same ! I have date +year / month date automaticaly and i can use chronology function :

       

       

      To move forward i created a calendar dimension table in power bi with the following DAX expression and i linked it with my other table to filter data. It works fine ;) :

       

      CALENDRIER = ADDCOLUMNS (CALENDAR(DATE(2010;1;1); DATE(2020;12;31)); "Année"; YEAR ( [Date] ); "N° Mois"; FORMAT ( [Date]; "MM" ); "Mois"; FORMAT ( [Date]; "mmm" ); "N°Semaine"; WEEKDAY ( [Date] ); "Jour"; FORMAT ( [Date]; "dddd" ); "Trimestre"; "T" & FORMAT ( [Date]; "Q" ))

      Thanks for your help !

      Ben