Well, it seems to me that the best 2 ways to prevent “Excel breaking Power BI report” should be: 1) First and foremost, education! Anyone involved in preparing dates should understand what is required. So, all of them should receive training. I’m not just talking about users preparing data in Excel. Because, in my experience, the deficiencies you are talking about (Changed Column names, Text in numerically formatted columns) occur even in the data provided by management systems - 2) Prevent breaks in Power Query as much as possible. for example: -to prevent breaks caused by empty rows, we could insert the remove blank rows step even if there are none when you prepare the model -to prevent breaks caused by removed Column or by whole new format of data, it is a good practice avoid any unnecessary applied step (such as changed type, reordered columns, renamed columns) that use column names -… By the way, Column order rearranged is NEVER a problem 🙂
Microsoft MVP • Freelance Power BI Consultant • 🎥 Power BI Park • #PBICoreVisuals Representative • #DataDNA Ambassador
Excel loaded to Power BI is really easy ... but also easy to mess up. Reasons my report has broken from Excel: -Text in Numerically formatted Columns -Whole new format of data -Column order rearranged -Changed Column names -Duplicate ID values -Removed Columns -Tables moved -Empty rows But sometimes it's inevitable, And Excel just happens to fit the needs. 2 ways to prevent Excel breaking your Power Bi report as often: - Don't use Excel, use dynamics or sharepoint lists (similar but more controlled) - Protect the cells (like header names) so they can't be altered. Do you do a lot of Power BI reports connected to Excel? #PowerBI #DataAnalyst #BusinessIntelligence
