Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Alchemista
Frequent Visitor

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

Excel data format.png

 

My dollar value is captured as "count of"

Power BI Data is captured as Count of.png

 

 

The Way Power BI gets it

Power BI Format.png

1 ACCEPTED SOLUTION

@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.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

View solution in original post

20 REPLIES 20
parry2k
Super User
Super User

@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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

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.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

@parry2k yes I have several blank cells as there is no data for some items (ie no last year sales thus the database shows blank)

 

Am I experiencing the formatting issues because of blank cells? How can I fill in the blanks?

@Alchemista also check this link and hope it helps.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

@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,

Anonymous
Not applicable

@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  go to modelling tab, select your column and change the format the way you want

 

image.png



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

@parry2k thanks a lot! I managed to somehow fix the Tonn Vol column and set it as whole number, couldnt edit the left SPPD column though. And while trying,  like I mentioned on my original post, I see the modeling tab greyed out. I dont know what caused it. How can I edit both columns separately? What makes the tab greyed out? Thanks,

 

How to remove decimals.png

Anonymous
Not applicable

@AlchemistaWhy don't change the SPPD column to whole number or decimal number in PQ and then after pushing to your Report view change the format as desired there?

@Anonymous I try to change format but do you know why my modeling/format tab is greyed out? How can I activate it? My table is selected as the screenshot below.

 

Untitled picture.png

Anonymous
Not applicable

@Alchemista  change your data type to any number (i.e. Whole number, decimal number) it will work!

This worked! Thank you.

@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.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

@parry2k @Anonymous Thanks for all the replies. One more question. Is there a way to bring in Nested type of data like seen on my excel screnshot below? Or does Power BI only handle 1 title row at a time? Like do I need to separate all these columns on their own tabs and then bring or is there a way to bring all this nested data and Power BI automatically picks up by timeline and region? Thanks,Nestled Data.png

@Alchemista there is many way to achieve these. All this can be different measures, lot of it going to be in modelling.

 

Logic is to bring as raw data as you can, and develop measures using DAX to meet requirements. 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

@parry2k @Anonymous As I try to clean up my data in Excel before bringing to Power BI, I wanted to ask how to repeat the cells you see in the screenshot below. My data comes with a title region/banner name and all data under it. But the region/banner column comes all merged per region and I unmerge it. However for Power BI to pick up each product line with banner information, I want to repeat these cells for each. In example below I fill the Circ K, then Shell etc the list goes on under. What is the efficient and quick way to do this?

 

How to repeat cells.png

 

Thank you,

@Alchemista as mentioned before, powerbi is giving type any becuase of blank cells. You need to change the column type in Power Query, you can keep Blank as it is



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Ok, I changed the "type" of one of my $ columns to "fixed decimal number" with screenshot below. Now how can I format it to clean it up like adding $ signs, commas for 000 and removing decimals? Thanks,

 

Changed $ Format.png

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.