Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |