Forum Discussion
How to import Excel data keeping the same column formatting? (%, $ and Unit Values showing properly)
Hello,
New to Power BI, I have a question regarding how to successfully import data into Power BI with the same formatting as I see on Excel.
In my data pull I have several facts like region, store banner, unit sales and growth percentages. However although i changed column formats to proper number and percentage on excel before i bring it to power bi, when i import to query, the data loses all formatting and looks very messy with no commas, no percentages and many decimals. Probably related to formatting issues in Power BI, my unit values show “count of” not even real values. The modeling tab is greyed out so i couldnt add commas for formatting after saving the query. You can see pics below for reference.
How can I import and manipulate typical sales units and sales growth type of data properly? How to remove decimals for both $, units and %? Is there an example/guide video you can share?
Thanks in advance.
EXCEL FORMAT
My dollar value is captured as "count of"
The Way Power BI gets it
Alchemista your modelling tab will be disabled if data type in power query is text or any (abc/123). as soon as you changed the data type to number/whole number , you have format options.
20 Replies
AlchemistaIf I understood correctly you want to replicate the column format in PBI as excel?
After pulling your dolumns to Power Query, you might have a step which the PQ engine changes the format to the best of its peception of your data. you can click on column header to change any of the column data format to your desired format. IF you have some formula in your excel file that is changed undesirable in PBI you ca always manipulate them in Power Query to get them correct for final reporting.
Hope it helps!
Anonymous thanks for the reply! I added some screenshots at the bottom of my original post if it will help you understand. As you can see from the screenshots, I already changed the percentage columns so it shows proper %. But how do I remove decimals, add commas for 000 for $ and units and how can I make Power BI import my excel files the way they are originally without the need for formatting the columns again in Power BI query?
Thanks,
AlchemistaAs I mentioned in my post, you can change the format on column headers and if later you need to remove decimal, round up, or add more decimal places and 000, you can apply your query in PQ and do all these next changes in PBI report view. Please consider that in PBI you have three different environment. You should try to clean your data and formats first in Power Query then after applying to PBI(pushing your data to reporting view) you can then do these manipulations there.
If still not sure, please upload your excel file in Dropbox and leave the link here so that I can do the job as you desire...
Alchemista can you share screen shot? Do you have $ sign in excel file, does powerbi taking those columns as text as $ sign part of value and making it text instead of number. You have check all that in PowerbI
Hi parry2k, just added some screenshots to my original post! Also corrected $ to Units as for this example I was using units, but same logic as I try to remove decimals and add commas for 000.
parry2k wrote:Alchemistacan you share screen shot? Do you have $ sign in excel file, does powerbi taking those columns as text as $ sign part of value and making it text instead of number. You have check all that in PowerbI
Alchemista looks like it it is taking those column as type any may be there are some blank/empty cells in your data and that's why it is making it as type any. You can always change is in Power Query though.
