cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
davidwhite83
Frequent Visitor

Power Query CSV File is not in a usable format

 
 

I am currently working with a CSV file that our company recieves where portions of the data are not on the same row as the invoice number, date, and other information. If it was a small file I could use excel formulas to copy the information down under it reaches the next invoice number but this is rather cumbersome and looking for an alternative work around to be do this automaticly after the setup is complete.

 

Below is a screen shot of the data.

 

 

 Screen Shot.png

 

I need the RONUMBER, ROCLOSED, ETC. to be on the same line as the OpCode (i.e. 52HOZ01).

 

After I have formatted the information properly than I can use in PowerBI for great visualizations.

 

Thanks.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User IV
Super User IV

Re: Power Query CSV File is not in a usable format

Hi @davidwhite83 
One more step. In those columns that have blanks rather than null, you have to highlight the column and perform Find and Replace. Leave the Find box empty and then type null into the lower box. All lowercase, no quotes. This should do it.  What is strange is that just typing it into your source table does not work.  Let me know how this goes.


If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

3 REPLIES 3
Super User IV
Super User IV

Re: Power Query CSV File is not in a usable format

Hi @davidwhite83 ,
If I understand correctly, starting from the bottom, you have a set of rows that you wish to align with the lower set of values. When you bring the file into Power Query there should be some null values. By going to the Transform tab, and clicking Fill Down, you will be able to fill in those lower rows. Then going back to the Home tab, Remove Rows, Remove Top Rows you will be able clean up the file.  Check out M is for Data Monkey by @KenPuls . A great book for all this sort of clean up and more.

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathanielpq123.PNG

 




pq12.PNG

 

pq1.PNG

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




davidwhite83
Frequent Visitor

Re: Power Query CSV File is not in a usable format

It works on the number portion of the file but not with names.Screen Shot.png

Super User IV
Super User IV

Re: Power Query CSV File is not in a usable format

Hi @davidwhite83 
One more step. In those columns that have blanks rather than null, you have to highlight the column and perform Find and Replace. Leave the Find box empty and then type null into the lower box. All lowercase, no quotes. This should do it.  What is strange is that just typing it into your source table does not work.  Let me know how this goes.


If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors