Forum Discussion
Issue: Promoting Headers removes all other rows
A table definition in Excel should work just fine. Power BI will read that meta data and pull the entire table contents, based on the current size of the table.
NOTE: This statement is only valid for rows of course. Dynamic columns are not well supported.
Show the data before promotion. And share your full m code (view/advanced editor + copy/paste)
Hey thanks. I think I figured out what was going on.
Not sure if there is a workaround but if you set a dynamic named range in excel and injest the data into power query, at first it show the entire dataset. But the moment you try and run a function or anything on the dataset, it somehow drops all the rows.
I made the dynamic named range with this formula in excel.
=$CG$7:INDEX($CG$7:$CU$2102,COUNTA(CG8#)+1,COUNTA($CG$7:$CU$7)-1)
What it does is expand or contract the named range based on the row and column count.
My ultimate goal was to set up a process where I can "enrich" certain data from using the info from this named range. Since the size of the table changes, I figured it would be good to have a dynamic named range. The issue is that PowerQuery doesn't seem to play well with Dynamic Named ranges.
I can't use a table because I need the spill functionality to adjust the size of the range.
Not sure if there are any good workarounds.
A table definition in Excel should work just fine. Power BI will read that meta data and pull the entire table contents, based on the current size of the table.
NOTE: This statement is only valid for rows of course. Dynamic columns are not well supported.