To find which words appear most often in a list of sentences, such as survey comments or product reviews, Power Query can split the text into words, count each one and sort the list, all refreshable with one click. StepsSplit → Unpivot → Group By → Sort Works inExcel 2016 and later Example100 English proverbs … Read more
Power Query’s Merge compares two tables and returns matching or non-matching rows, depending on the join kind. Here are all six, explained with two cast lists, and the steps to use any of them. WhereData → Get Data → Combine Queries → Merge Join kinds6 Works inExcel 2016 and later, Power BI The six joins … Read more
Power Query's Group By turns a long list of transactions into a summary: total sales per product, count of orders per region, or several totals at once. Unlike a formula summary, it refreshes with one click when new rows arrive. WhereHome → Group By OperationsSum, Average, Min, Max, Count Rows, Count Distinct, All Rows Excel … Read more
To turn rows into columns, use Paste Special → Transpose for a one-off, the TRANSPOSE function for a live copy, or Power Query for a version that refreshes and can be cleaned on the way. One-offPaste Special → Transpose Live formula=TRANSPOSE(B2:G4) RefreshablePower Query → Transpose The data Power Query Click in the data → Data … Read more
Power Query’s Index Column numbers every row and renumbers when you refresh, so new or removed rows never break the sequence. You can start at any number and add prefixes like INV-0150. WhereAdd Column → Index Column RefreshRenumbers automatically Works inExcel 2016 and later Step 1: Load the data Click in the data → Data … Read more
Reports often show a group name once and leave the rows below it blank. Power Query’s Fill Down copies each value into the empty cells beneath it, and Fill Up copies upwards, so the data is ready for a PivotTable. WhereRight-click a column → Fill → Down or Up Fillsnull cells only Works inExcel 2016 … Read more
Power Query's Text.Reverse reverses each text string in a column, so Ajay becomes yajA. Wrap it in Text.Proper to tidy the capitals, and refresh whenever the list changes. M functionText.Reverse Tidy capitalsText.Proper(Text.Reverse(…)) Works inExcel 2016 and later, Power BI Step 1: Load the list Click in the list → Data → From Table/Range → OK. … Read more
Normalized Data using Power Query in Excel
Merge worksheets using Power Query in Excel