To take advantage of this feature, on the View tab of the Power Query Editor, check the Column quality, Column distribution, and Column profile boxes, as shown in Figure 3. With this feature, you can quickly identify potential problem areas such as erroneous data, missing records, and even duplicated records. These transformations, for example, could include items such as deleting unnecessary columns of data, merging columns, adding user-defined calculations, and sorting and filtering as part of the query, among other items.Ī recent enhancement to Power Query is its’ ability to automatically analyze your data for quality issues such as completeness and accuracy. Perhaps more importantly, you can use Power Query to transform your data to make it more useful to you. You can use this utility to query data into Excel from countless external data sources, including the databases supporting most major accounting applications. XLOOKUP allows you to specify what happens if your lookup value isn’t found, without having to include an IFERROR function.įirst available with the 2010 release of Excel, the evolution of Power Query is nothing short of remarkable. This is because the function can look to the left or right when using it as an alternative to VLOOKUP. Likewise, it can look above or below when using it as an alternative to HLOOKUP. The arrangement of columns and rows does not matter with XLOOKUP.
Most will also find XLOOKUP to be even more powerful. Some of the critical differences between XLOOKUP and other lookup functions include: While these legacy functions will remain in Excel, many users will find XLOOKUP to be more straightforward and intuitive. XLOOKUP offers a superior alternative to VLOOKUP and similar functions such as HLOOKUP and INDEX. Microsoft added XLOOKUP to Excel provided through Office 365 beginning in February 2020.
0 kommentar(er)
