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
Mark-FS
Frequent Visitor

Dealing with blanks when transforming a CSV file

I am very "new" to Power BI and  am trying to transform a CSV file that contain columns with blank cells, but in the row the fact that the cell is blank has relevance in relationship to another cell in the same row. As I upload the CSV file, the blank cells all come up with "null" but a replacement of a "0" for the blank (null) cell has a direct impact upon the meaning of the data so can't be used. How do I get the transform to accept that certain cells are actually blank?

5 REPLIES 5
parry2k
Super User
Super User

@Mark-FS what you are saying is the blank cell has a value of 0 where you expect blank or another way around?



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.

In this particular instance where I'm doing a reconciliation of invoices to orders, the order column will have an order number but if the invoice hasn't been received, the invoice number column will be blank. A "0" within this field could be construed as an invoice number and would have a number of "duplicate" invoice numbers which causes an issue within itself. I'm looking to have the cell recognised and "accepted" into the tables as being blank..

Hope that makes sense..

Hi @Mark-FS 

 

As you may have multiple blank invoice numbers for different invoices, replacing the blank with a specific value is not a good idea. Is there any other column in your table that can provide a unique value for each invoice? Or is it possible to combine several columns like [DateTime]&[Region]&[Department]&... to generate a unique value? If it's possible, you can add a new order number column to the table that generates a unique value for each blank order number. Use original order numbers for non-blank rows. Then use this column to build relationships with other tables. 

 

Usually order number column is used to identify each order and should not accept blank values. It would be better to generate a unique id for it when collecting and recording data. For other missing values, you could do some google searching to get more ideas. For example Dealing with Missing Values for Data Science Beginners

 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

Hi,

 

Many thanks for the response and am in agreement with what you've indicated. To better explain my issue, here is an exampele of the data:

Order DateOrderIDStore IDOrder QuantityOrder ValueInvoice NumberInvoice QuantityInvoice Value
2021-07-13107886609276467   1 601.00nullnullnull
2021-05-0510742071814083      522.00nullnullnull
2021-04-301073913342582314      800.00nullnullnull
2021-07-1510790079125712      320.00nullnullnull
2021-04-071072316867532572      480.0034822431      276.00
2021-09-10108308219374004      508.00nullnullnull
2021-02-2510696253603319619   2 318.00347734122   3 086.60
2021-11-241088575379762064   8 448.00351081045   6 831.00
2021-10-12108538843425152      264.00nullnullnull
2021-02-021067922469313982      244.00nullnullnull
2021-06-0410763036564853919   2 318.0034895357      982.10



When bringing in the data, the "blank" cells come up with "null" and as a results the transformation creates an "error". The reality is that the blank cells have meaning insofar as the lack of data means that the order has not been invoiced. Inserting a "0" is not the solution as it could be presented as the "invoice number" resulting in duplicate values.

 

I'm looking to find a way in which the "blank" cells can be "accepted" as it is within the transformation. I believe that the accepted rule is that missing fields should be excluded but that defeats the purpose of the reconciliation exercise I'm trying to complete.

 

Does this explain my issue better?

 

Regards

Hi @Mark-FS 

 

Maybe you can change Invoice Number column to Text type instead of Number type so that it can accept null as values. 

 

Regards

Jing

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.