Blog Posts on Different Topics of Power Query in Excel
This blog post is about different ways to find the ‘Most Repeated Number or Text’ in a data set. Formula to find the most repeated Number The MODE function in Excel is designed to return the most repeated number from a numeric dataset. An example with vertical data ranges. Another example with a 2 dimensional … Read more
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
To get 0.159 from 4.159, subtract the whole-number part: =B3-TRUNC(B3). MOD and Power Query work too, but they handle negative numbers differently, so pick the method that gives the answer you need. Formula=B3-TRUNC(B3) Always positive=MOD(ABS(B3),1) Whole part=TRUNC(B3) Formulas compared Formula 4.159 −4.159 =B3-TRUNC(B3) 0.159 −0.159 =ABS(B3-TRUNC(B3)) 0.159 0.159 =MOD(B3,1) 0.159 0.841 =MOD(ABS(B3),1) 0.159 0.159 MOD … 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
1 + 2 + 3 + … + 50 = 1,275. It's a classic puzzle, and a neat way to compare Excel tools: a one-line maths formula, SEQUENCE, SUMPRODUCT and ROW, Power Query and VBA all get there. Answer1,275 Shortest=50*51/2 Excel 365=SUM(SEQUENCE(50)) 1. The maths formula =50*(50+1)/2Copy The sum of 1 to n is n(n+1)/2. … Read more
To get the day of the week for a date, such as Tuesday for 28-Dec-1937, use =TEXT(B3,”dddd”). Here are four ways: two formulas, a number format that shows the day without changing the date, and Power Query, plus day names in Malayalam and Hindi. fx=TEXT(B3,”dddd”) ProblemShow the weekday name for a date Functions usedTEXT, or … Read more
To split a column of amounts into positive and negative columns, such as receipts and payments in a bank statement, use =MAX(B3,0) and =MIN(B3,0), an IF formula that leaves blanks, or FILTER for two separate lists. Power Query does it for large data. fx=MAX(B3,0) ProblemSplit amounts into positive and negative columns Functions usedMAX, MIN, IF, … Read more