There are multiple ways to accomplish this, but we’re going to start with the basics. So… How do you combine / append / stack tables with Power BI / Power Query? Using List.I’ve previously done a series on Merge / JOIN operations ( First Part here) and it’s now time to do one on Combine / Append operations. Return Row Based on Max Value From One Column when Grouping Group By In Power Query When Data In Column ChangesĪdd a Refresh Time Stamp To Your Power BI Reports

Use List.PositionOf() To Find The First Row of Data In An Excel File Renaming A Column In Power Query Based On Position Replace Power BI Alerts with Power Automate Alerts for SQL Server Data Working With Multiple Row Headers From Excel in Power QueryĬhange The Day Your Week Starts In Power Query Quickly Format All DAX Code in Tabular Editor Working With Sparklines In Power BI - All About The Filter ContextĪdd an Animated GIF to Your Power BI Reportsīe Careful When Filtering for Blanks in DAX Working With Multiple Windows in Tabular Editor 3 Why You Should Avoid Calculated Columns in Power BI This method is easy because it stays in the Power Query UI, where you are comfortable working. All of those work well, but it also makes it harder for you to get at to edit if you need to in the future.

Maybe you have some data you want to be hidden, or at least difficult to find or modify, and don’t want to fool with hiding sheets, locking workbook properties, or setting worksheet’s hidden property in VBA to xlVeryHidden. The underlying Power Query tool is very similar to the one in Power BI though, so all of that Create Table code is still there. That is probably why the Excel team didn’t bother to expose this functionality anywhere in Excel. Why would you do this though? Unlike Power BI, Excel has a place for this kind of data, in a Table in the worksheet you import into Power Query. Power Query is just storing this data as a compressed binary string of characters, and can usually store up to 3,000 cells. I keep that code snippet in Evernote where I can do this in Excel anytime I need to without firing up Power BI to get the code. The Create Table dialog shows up and you can add and change data and records however you like.

Power query excel 2019