TOROW Function in Excel: Flatten Into One Row
TOROW flattens a block into a single row, optionally skipping blanks and errors.
Read article →Excel
Excel formulas, unusual formula combinations, VBA, Power Query and dashboards — from basics to clever tricks.
Look Up the Price Valid on a Date in Excel (Rate Changes, Price Lists, Tax Rates)
Look Up the Price Valid on a Date in Excel (Rate Changes, Price Lists, Tax Rates)
Pick the rate that was valid on a transaction date from a price history — XLOOKUP with match mode -1, LOOKUP, MAXIFS + INDEX…
Read article →Power Query Lesson 10: Project — A Monthly Refresh Pipeline
Power Query Lesson 10: Project — A Monthly Refresh Pipeline
Build a complete refreshable pipeline: combine monthly files from a folder, clean and type them, merge product details, add FY columns, load to the…
Read article →Power Query Lesson 9: Dates, Financial Year and a Calendar Table
Power Query Lesson 9: Dates, Financial Year and a Calendar Table
Work with dates in Power Query: extract year, month name and week, add Indian financial year and FY quarter columns, calculate days between dates,…
Read article →Power Query Lesson 8: Custom and Conditional Columns (a Little M)
Power Query Lesson 8: Custom and Conditional Columns (a Little M)
Add calculated columns in Power Query: Conditional Column for size bands, Custom Column formulas for GST and totals, Column From Examples, if-then-else in M,…
Read article →Power Query Lesson 7: Group By
Power Query Lesson 7: Group By
Summarise data inside Power Query with Group By: basic and advanced grouping, several aggregations (sum, count, count distinct), grouping by two columns, and when…
Read article →Power Query Lesson 6: Append and Combine a Folder of Files
Power Query Lesson 6: Append and Combine a Folder of Files
Stack tables with Append Queries, then combine every file in a folder with From Folder: sample file transform, file name as a column, filtering…
Read article →Power Query Lesson 5: Merge Queries — Lookups Without VLOOKUP
Power Query Lesson 5: Merge Queries — Lookups Without VLOOKUP
Bring product names and categories into an orders table with Merge Queries: matching columns, join kinds (left outer, inner, anti), expanding columns, and finding…
Read article →Power Query Lesson 4: Unpivot and Pivot
Power Query Lesson 4: Unpivot and Pivot
Turn a cross-tab with months across the top into a proper data table with Unpivot Other Columns, pivot data back into columns, and know…
Read article →Power Query Lesson 3: Removing Junk Rows, Duplicates and Errors
Power Query Lesson 3: Removing Junk Rows, Duplicates and Errors
Clean an ERP-style export: remove top rows, promote headers, fill down group labels, filter out subtotals, remove duplicates and rows with errors, and keep…
Read article →Power Query Lesson 2: Cleaning Text — Trim, Case and Split
Power Query Lesson 2: Cleaning Text — Trim, Case and Split
Clean messy text columns in Power Query: Trim and Clean, Capitalize Each Word, Replace Values, Split Column by delimiter, and Extract text — the…
Read article →GAMMAINV Function in Excel: Inverse Gamma (Older Name)
GAMMAINV Function in Excel: Inverse Gamma (Older Name)
GAMMAINV returns the value at a gamma probability. It is a compatibility function: Excel keeps it so workbooks from Excel 2007 and earlier still…
Read article →