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.
I have a spreadsheet that contains scores of tables of survey data analysis. I'd like to find a way to split them out into multiple tables in my data model. I've decribed the format below and added a link to a sample. I've read about the Table.Split function, but there are no set number of rows that I can use for the pageSize. I was thinking if I can calculated the pageSize number by counting the number of rows between two strings I could pull this off, but it seems like Table.Split doesn't work like that. Is there something else I can do, or should I just split out these tables manually? Thanks in advance.
Link to file
The format goes like this:
Row 1 is blank
A header in row 2 that reads "Custom Table"
A blank row
A row with a subheader that reads "Notes" followed by a table with data that I don't care about
2 blank rows
A row with a path in the first cell
2 blank rows
A header in the next row that reads "Table 1"
A blank row
A table with data that I care about in the form of a matrix: question responses in the rows, categories in the columns, and numbers in the cells
Another blank row after the table
Another subheader followed by a table with data that I don't care about.
1-3 rows of notes
2 blank rows
Then the process repeats
Solved! Go to Solution.
Did you get the "suggested tables" feature on the Navigator screen (bottom left) when you connect to the Excel spreadsheet?
How did I miss that? Yep, that works. Thank you for pointing that out.
The automated code generated is quite impressive.
Also, there's another technique where you go into Excel and define a range on each table and powerbi can recognise that.
Good luck
Did you get the "suggested tables" feature on the Navigator screen (bottom left) when you connect to the Excel spreadsheet?
How did I miss that? Yep, that works. Thank you for pointing that out.
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.