Importing file from sharepoint with number in custom format

[menphis21]

Hi Everyone,

I am importing an Excel file from sharepoint. In the table the field that i need to import have a custom format :

#,##0,_);(#,##0,);_ * - ??_

So it means that appears 5,286 instead of 5,286,584.

The problem is that when power query import this number, it import it like 5,286 so i am losing the millions number.

how can i solve that without touching the excel file ?

Thanks,

Avy

[menphis21]

@Ashish_Mathur 
help please 🙂

[Ashish_Mathur]

Sorry do not know.

[Anonymous]

Hi @menphis21 ,

Please have a try.In Power Query Editor, select the column that contains the custom format.

• Click on the "Replace Values" button in the "Transform" tab.
• In the "Replace Values" dialog box, enter the custom format in the "Value to Find" field and enter the standard format in the "Replace With" field. For example, enter "#,##0,);(#,##0,); * - ??_" in the "Value to Find" field and enter "#,##0" in the "Replace With" field.
• Click on the "OK" button to replace the values.

After you replace the values, you can change the data type of the column to "Whole Number" or "Decimal Number" to include the millions number. You can also use the "Round" function in Power Query to round the numbers to the nearest whole number.

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

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

[menphis21]

HI @Anonymous

Thanks for your help. It does not work.
But when I wanted to create an example file for you to send to you, I created a tab copied to a new Excel file and imported it to powerbi to reverify and it works with the correct format without making any changes.
When I import the original file, it still doesn't work.

Of course, i cannot change the original file

[menphis21]

@Idrissshatila @amitchandak @Ritaf1983 @Greg_Deckler @lbendlin @Ahmedx 

[menphis21]

@Anonymous @Idrissshatila @amitchandak @Ritaf1983 @Greg_Deckler @lbendlin @Ahmedx 
Please help