Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Incorrect Data being pulled from Excel Spreadsheet in SharePoint

I have an Excel Speadsheet located on Sharepoint which feeds into a Power BI Query. There are two columns containing dates, and a third column which holds a formula calculating the percentage of time...
  • Anonymous's avatar
    Anonymous
    8 years ago

    Anonymous,

    Do you firstly connect to the Excel located on SharePoint Online in a new Excel file using Power Query option and then create custom column to calcultate the percentage in Power Query?


    After that you import the Excel file that contains the custom column  to Power BI?  If so, I can't reproduce your issue. Could you please share your formula? Also why not directly connect to the Excel located in Sharepoint in Power BI Desktop and then create the percentage column?

    Regards,
    Lydia

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Anonymous,

Do you firstly connect to the Excel located on SharePoint Online in a new Excel file using Power Query option and then create custom column to calcultate the percentage in Power Query?


After that you import the Excel file that contains the custom column  to Power BI?  If so, I can't reproduce your issue. Could you please share your formula? Also why not directly connect to the Excel located in Sharepoint in Power BI Desktop and then create the percentage column?

Regards,
Lydia

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Hi Lydia,

 

All three columns (Both dates, and the calculated percentage) are located on the Sharepoint Excel File. The value from the Excel file is diffrerent from the value on the Power BI Query. I don't use a caluculated column. The formula in Excel is 

=IF([@[First Date]]<TODAY(),100%,IF([@[First Date]]>TODAY(),0%,(100%/(DAYS([@[Second Date]],[@[First Date]])))*(DAYS(TODAY(),[@[First Date]]))))

The value shows at 17% on Excel, and is updated everyday. But on Power BI the value is stuck at 13.4%.