cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
tchristy99
Helper II
Helper II

Splitting Query at null rows

I have excel files that are being dropped into a folder weekly. I am trying to use BI to pull them into a report, I don't have the ability to edit them before hand though. The stucture of these file are as follow:

 

Column1Column2Column3Column4Column5Column6

rows of data

     

null

nullnullnullnullnull

null

nullnullnullnullnull

Column1

Column2Column3   

rows of data

     

null

nullnullnullnullnull

null

nullnullnullnullnull
Column1Column2Column3Column4Column5 
rows of data     

 

Is there anyway to split the query at the nulls? Or filter out everything below the nulls? I am interested in any full or partial ideas, because I am out of them.

 

Thanks

6 REPLIES 6
v-stephen-msft
Community Support
Community Support

Hi @tchristy99 ,

 

I found this similar post you can refer to

Split table in multiple tables by blank rows

 

Best Regards,

Stephen Tao

 

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

watkinnc
Super User
Super User

Since you are working with a folder, I take it that at some point, you have a column of tables?  If so, let's say the table column name is "Data".  You can try:

 

FilteredNulls = Table.AddColumn(PriorStepName, "Filtered", each Table.SelectRows([Data], each [Column1] <> null))

 

Now the tables should each have been filtered to remove the nulls from each Column1 of your tables.

 

--Nate


I’m usually answering from my phone, which means the results are visualized only in my mind. You’ll need to use my answer to know that it works—but it will work!!
v-stephen-msft
Community Support
Community Support

Hi @tchristy99 ,

 

You can try to convert them into tables in Excel first, so that three tables will appear when you import Power BI.

Select the table you want and press Ctrl+T to generate the table. .

3.png

4.png

 

Then you can directly get the two tables when importing Excel.

5.png6.png

 

 

Best Regards,

Stephen Tao

 

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

 

Hi,

 

Thanks for your response. I did consider that, but these files are being dropped in daily, so I need something on the BI side. It is very possible that its not possible.

negi007
Super User
Super User

@tchristy99 have you tried using remove rows and keep rows option. those option may help you in acheving the output that you are looking for. 

 

negi007_0-1621605892977.png

 

If above does not help you, can you share the screenshot of the final output basis the input data that you have shared above. thanks




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



Proud to be a Super User!


Follow me on linkedin

Hi, 

 

Thanks for your quick response.

 

tchristy99_1-1621622261151.png

 

That won't work because the amount of rows changes from file to file. Above is a image of the data (with a lot of rows hidden). What i need is them in 3 different tables in Power BI.

 

 

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors