Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.
Hi all,
I have a quite specific question for you :
I made a query with parameters to get the values of multiple excel files (hosted on sharepoint) into one single table, that I will call "multiple file table" or "consolidation table".
Because all these files have exactly the same structure, I can see them in one single Power BI Table with same columns for each.
In the applied steps of the Table, I had to remove the First 10 rows of the Excel file (it contains useless values), but it just did it for the 1st file I injected.
What I would like is : create a formula to remove first 10 rows each time my Table is processing a new file.
(What I did until now is a series of "= Table.SelectRows(#"Renamed Columns", each [field1] <> null and [field2] <> null" ... but it is quite ugly and not developer friendly.)
I hope it is clear for you.
Many thanks in advance
Solved! Go to Solution.
Hi @stretcharm, the project is quite old now, and I found another way to do it.
What I did :
-I didn't use the function 'Use first row as headers", and I added a conditional column called "UselessRowsFlag", that can flag with a "X" each time these rows start, and each time they end, by a simple "If" statement.
-at the end of the Power Query steps, I removed all "X" occurencies in that column.
Many thanks anyway ! 😉
Have you used the combine from a folder to load all your files?
If so can you add the code to the function so the for each file it loads to remove the top 10 rows.
I loaded some xls and it generated this function to combine them. I then added the Table.Skip to remove the 10 rows for each file
let Source = (#"Sample File Parameter1") => let Source = Excel.Workbook(#"Sample File Parameter1", null, true), Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data], Sheet1_Sheet2 = Table.Skip(Sheet1_Sheet,10) in Sheet1_Sheet2 in Source
Hi @stretcharm, the project is quite old now, and I found another way to do it.
What I did :
-I didn't use the function 'Use first row as headers", and I added a conditional column called "UselessRowsFlag", that can flag with a "X" each time these rows start, and each time they end, by a simple "If" statement.
-at the end of the Power Query steps, I removed all "X" occurencies in that column.
Many thanks anyway ! 😉
Cool. Sorry didn't realised the post was so old.
That would have been my other solution for binning unwanted rows.
Check out the November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.
Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!