Add Column From Examples

One option that can make complex data transformations seem simple is the feature called Add Column From Examples. Using Add Column From Examples, you can provide the Power Query Editor a sample of what you would like your data to look like, and it can then automatically determine which transforms are required to accomplish your goal. Continuing with the same failed banks example, let's walk through a simple example of how to use this feature:

1. Find and select the Add Column tab in the Power Query Editor ribbon.
2. Select the Column From Example button and, if prompted, choose From All Columns. This will launch a new Add Column From Examples interface.
3. Our goal is to leverage this feature to combine the City and ST columns together. In the first empty cell, type Chicago, IL and then hit Enter. You should notice that below the text you typed Power BI has automatically translated what you typed into a transform that can be applied for every row in the dataset.

4. Once you click OK, the transform is finalized and automatically added to the overall M query that has been built through the user interface: