Excel’s From PDF connector pulls tables out of a PDF into Power Query, where you can clean them and load them to sheets. Replace the PDF with a newer one and click Refresh to update. WhereData → Get Data → From File → From PDF Works inMicrosoft 365 and Excel 2021 for Windows Not forScanned … Read more
Excel's Remove Duplicates button deletes repeated rows in one click. When you need to keep the original, or the data keeps growing, use UNIQUE, Advanced Filter, Power Query, a PivotTable or a macro instead. One clickData → Remove Duplicates Keeps the originalUNIQUE, Advanced Filter, Power Query RefreshablePower Query Which method? Method Changes the original? Updates … Read more
Two different jobs share this name: pulling the number cells out of a mixed column, and pulling the digits out of text like INV-2041A. Here are formulas and tools for both. Number cellsFILTER + ISNUMBER, Go To Special, Power Query Digits in textTEXTJOIN + MID, REGEXEXTRACT Works inAll versions (dynamic formulas: 365) The data Part … Read more
To turn Hana into anaH, Excel 365 can do it with one formula. In older versions, use a one-line VBA function, or Power Query’s Text.Reverse. Excel 365CONCAT + MID + SEQUENCE Any versionStrReverse in VBA Power QueryText.Reverse 1. Formula (Excel 365) =CONCAT(MID(B3,SEQUENCE(LEN(B3),,LEN(B3),-1),1))Copy Step For “Hana” LEN(B3) 4 SEQUENCE(4,,4,-1) {4;3;2;1}, counting down MID(B3,{4;3;2;1},1) {“a”;”n”;”a”;”H”} CONCAT(…) anaH … Read more
To see how many times each item appears, use UNIQUE and COUNTIF in Excel 365, GROUPBY for a sorted summary, a PivotTable, or Power Query’s Group By. All four update when the list changes. Excel 365UNIQUE + COUNTIF, or GROUPBY Any versionPivotTable RefreshablePower Query Group By The list The list is an Excel Table named … Read more
To find the names, codes or items that appear in both lists, test each value with COUNTIF, pull out the matches with FILTER, or highlight them with conditional formatting. Power Query’s inner join does the same for large tables. fx=FILTER(B3:B10,COUNTIF(D3:D9,B3:B10)) ProblemList or mark the values that are in both lists Functions usedCOUNTIF, FILTER, MATCH Works … Read more
To flip a list so the last item comes first, sort by a helper column, use a formula, or in Excel 365 use one SORTBY formula that updates automatically. Power Query’s Reverse Rows does it for whole tables. Excel 365SORTBY + SEQUENCE Any versionINDEX + ROWS No formulaHelper column and sort The list 1. SORTBY … Read more
Power Query normally hard-codes values such as a year or a row count into its steps. Point those steps at a cell instead, and anyone can change the cell and click Refresh All to get new results, without opening the editor. TechniqueTable → Drill Down → use in M code ExampleTop N films of any … Read more
Power Query converts text to UPPERCASE, lowercase or Capitalize Each Word in two clicks, and the result refreshes when the source changes. Use Transform to change a column in place, or Add Column to keep the original. WhereTransform → Format, or Add Column → Format M functionsText.Upper, Text.Lower, Text.Proper Works inExcel 2016 and later, Power … Read more
Power Query’s Text.Select keeps only the characters you list, and Text.Remove throws them away. With one custom column you can strip !@#$%&* from messy text, or pull out just the numbers, capitals or letters. FunctionsText.Select, Text.Remove Works inExcel 2016 and later, Microsoft 365 RefreshableYes The data Step 1: Load the data into Power Query Click … Read more