Forum Discussion
Expand excel table but limit columns
Hi,
Why is it fetching columns which are blanks into the Table? My guess is that there is formatting lying in the cells which look like blank cells to you.. Please open the source Excel file and delete the empty rows and columns. Save the file and close the file.
Ashish_Mathur thanks. i will try it again. that was one of the first things i did prior to putting the thread up
Hi,
Why is it fetching columns which are blanks into the Table? My guess is that there is formatting lying in the cells which look like blank cells to you.. Please open the source Excel file and delete the empty rows and columns. Save the file and close the file.
Ashish_Mathur thanks. i will try it again. that was one of the first things i did prior to putting the thread up
Ashish_Mathur in the end i re-did the whole query and that solved the problem
but on comparison with the new query, all steps are the same but behave differently. anyhow looking good
thanksGood to know that.
Anonymous
Won't you still need to go through 16,000+ columns?
Will the source file be regenerated thereby creating all 16,000+ columns again?
Download sample PBIX with code shown below
You can filter out all empty columns using Power Query by:
1. Unpivoting the columns
2. Filtering out the blank rows (these were the empty columns before unpivoting)
3. Pivoting
You could also write a custom function to select only the columns that are not empty. I'll write this if you don't want to use the Unpivot/Pivot approach.
Regards
Phil
Phil
