Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |