Home / Works / Power Query Consolidation Pipeline

Power Query Consolidation Pipeline

Excel · M Language
Weekly Excel · Power Query (M)

Overview

Locates and loads source databases from a configured folder (skipping lock files), combines them, joins them to tranche, conversion and reclassification reference data, flags consolidation status, and normalises dates and currency.

How it works

The reference table is used twice: to pick barangays and to flag each row.

Build path + fileprefixes (in workbook) List folder; keepmatching .xlsx; skip ~$ Open each file,take area table Combine tables; drop rowswithout index number Find barangays holdingreference areas Build referencetable Keep all rows fromthose barangays Flag CONSO / NON-CONSOby reference match Consolidatedbuild table Error query: rows withtype mismatches

InputStepDecision / optionData storeOutput

About the code

Built for my department. The code stays with the company, so this page describes the tool only.