Custom Data Types in Excel
Yesterday, I demonstrated how to use Excel data types to give cell content richer meaning.
Issues like erroneous and missing data damage their credibility though, and it’s probably a big reason why Microsoft won’t be renewing their Wolfram license, which ends on June 11, 2023.
Power Query custom data types offer an alternative that gives you greater control and flexibility. You choose what data is stored instead of relying on a provider.
This example extracts Premier League club data from Wikipedia for the 2022/23 season.
Steps
- Select Data (tab) > Get & Transform Data (group) > From Web (button).
- Paste ‘https:// en. wikipedia. org/wiki/2022%E2%80%9323_Premier_League’ as the URL and click OK.
- Double-click the Personnel and kits[edit] item to open the Power Query Editor.
- Click on the first column and then press Ctrl + A to select all of them.
- Select Transform (tab) > Structured Column (group) > Create Data Type (button) to open the Create Data Type window.
- Type ‘Premier League Teams’ for the Data type name and click OK.
- Select Home (tab) > Close & Load (button) to load the table into a new worksheet.
- Activate A2 and click the in-cell Show card button.
- Click the Extract to grid button next to each field (apart from Team) to add them to the table.
Currently, there is no way of storing images, but I’m sure it won’t be long before we can.