Use the Schema Tools in Microsoft Dataflows to Manipulate Columns

A new view (pictured above) was recently spotted recently “in the wild” in Power BI dataflows (credit for the find: Dave Rujiter). I can’t find anything about it in the official documentation as of early August 2020. It’s not yet in Power BI Desktop, but I suspect this will be part of the August release.

This feature is a welcome addition that provides a column/schema centric vertical view of a dataflow, instead of the familiar data preview. “Wide” schemas with lots of columns are usually frowned upon in BI, but it’s pretty easy to have more columns than fit in one screen’s width in the data preview. This view lets you see and search all columns without scrolling horizontally. People coming from a relational database background will think it’s a no brainer. In my anecdotal observation, clicking through the steps feels faster than in the default data preview view.

You still get access to the formula bar and query properties, but the goal here is to manipulate columns:

  • Move, remove, or the usually recommended remove other columns. It’s better to select positively those columns you want to keep rather than select negatively those you want to remove, as the latter is more likely to break in case of schema drift.
  • Duplicate.
  • Rename.
  • Detect/change data types. Speaking of which, a long-standing bug where Power BI Desktop didn’t get proper datatypes from dataflows (date would be loaded as datetime) was recently fixed.
  • Mark as key, which was added to the UI a couple of months ago.

Schema Tools are also available in PowerApps and Azure Data Factory, suggesting that the underlying code base and deployment cycles for all versions of dataflows in Microsoft’s platform are common or at least in very close sync. Incidentally I’m puzzled that Azure Data Factory is not mentioned in the Power Query website, which would deserve more love, like having its own blog.

The same Schema Tools in ADF’s wrangling data flows

Leave a Reply

Your email address will not be published. Required fields are marked *