Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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:
Column1 | Column2 | Column3 | Column4 | Column5 | Column6 |
rows of data | |||||
null | null | null | null | null | null |
null | null | null | null | null | null |
Column1 | Column2 | Column3 | |||
rows of data | |||||
null | null | null | null | null | null |
null | null | null | null | null | null |
Column1 | Column2 | Column3 | Column4 | Column5 | |
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
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.
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
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. .
Then you can directly get the two tables when importing Excel.
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.
@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.
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
Proud to be a Super User!
Hi,
Thanks for your quick response.
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.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
24 | |
12 | |
12 | |
11 | |
8 |
User | Count |
---|---|
42 | |
25 | |
14 | |
14 | |
12 |