Large Volume - Flat file cleaning and adding to data model workflow

Hi jonsteele ,

You can streamline your workflow entirely within Power BI and eliminate the need for DAX Studio or Excel preprocessing. Start by using Power BI Desktop’s “Folder” connector to load all CSV files. When connecting, Power Query exposes the file metadata like Name and Folder Path. You can extract the entity identifier using something like:

= Table.AddColumn(Source, "Entity", each Text.BeforeDelimiter([Name], "_"))

or adapt to your specific filename structure. Once the raw files are loaded, apply transformation steps in Power Query to clean and normalize each of the three schema groups. Use conditional logic to manage schema differences:

= Table.TransformColumnTypes(YourTable, {{"Traffic Total (Volume)(RAW)", Int64.Type}})

Use custom columns to parse the “Date Time” field if it needs restructuring or splitting. After transformations, load the cleaned data into the Power BI data model.

Instead of exporting the cleaned data via DAX Studio, you can keep it within the model or publish the PBIX as a dataset to a shared workspace. If you want to enable other reports to reference the clean data, consider publishing this model as a shared dataset or use a Power BI Dataflow to house the logic and make the clean output accessible to other reports. Dataflows work under Pro licenses if everyone is in the same workspace.

To simulate incremental refresh without Premium, you can load your historical files once into one table, and append only the latest month’s files to another table before combining them in Power Query:

= Table.Combine({HistoricalTable, NewMonthTable})

You can also build a small validation page in Power BI itself with visuals that flag missing data, nulls, or duplicate timestamps using DAX like:

Has Nulls =
IF (
    COUNTROWS ( FILTER ( Data, ISBLANK ( Data[Traffic Total (Volume)(RAW)] ) ) ) > 0,
    "Yes",
    "No"
)

This keeps the verification interactive and avoids round-tripping to Excel or external tools. With this setup, you can go from raw file to clean model to published dashboard—all within Power BI Pro—without needing DAX Studio or creating intermediate CSVs.