Forum Discussion

Sookz's avatar
Sookz
Frequent Visitor
2 years ago

Help needed with data source in power query

Hi There, 

 

I have trouble with specific data source when transformed into power BI.  

Reference to the screenshot below, i particularly have problems with GOP03, GOP04, FC03 and FC04 columns. 

 

Instead of showing the absolute values (like the other columns), power BI is capturing the whole number only. 

The data set is in excel and are formulated. 

 

I have tried reloading the data source but it doesn't work. I am suspecting it might be the format of the data source in the power query that is causing this issue. Looking for a great person to enlighten me on this. Thank you so much! 

 

 

 

 

 

Regards, 

Sherry Cheong

 

Need Help

3 Replies

  • Sookz's avatar
    Sookz
    Frequent Visitor
    2 years ago

    An update to this: 

     

    This error happens only when new data is added to the data source and power query refreshes the file. 

    A new fresh upload of data set into power query doesn't have this issue. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi  Sookz ,

     

    Are you referring to the fact that decimal in Excel goes into Power BI and displays as number?

    You can do this in Power Query by selecting Columns - Transform - Data type - this will convert the data type of those columns to decimal, making sure to keep the decimal values.

    Refer to:

    Solved: Re: Power Query lost decimal values in numbers - Microsoft Fabric Community

     

    Best Regards,

    Liu Yang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Sookz's avatar
      Sookz
      Frequent Visitor
      2 years ago

      Hello Liu Yang, 

       

      Thanks for your reply! 

       

      Yes, the decimal display in excel goes into Power BI and becomes a whole number. When the data is loaded, the data type is fixed decimal number but the data is not showing the decimals. 

       

      When i tried to change the format to decimal number, it shows whole number instead. 

       

      Note: This only happens when the data source is updated and refreshed in power query. If i re-load the updated data set as a fresh new data source, i don't encounter this issue. 

       

      I looked through the link that you shared and it was mentioned that type change should be done using M code in the advanced editor. I realised the the data type is currency instead of decimal/fixed decimal/whole number. 

       

      Tried changing the data type in M code in advanced editor to decimal and i get the same result (data turns into whole number and the decimals becomes .00) 

       

       

       

       

      Regards, 

      Sherry Cheong