Reading data from CSV files with different columns

Yes, and it is actually very simple.  Table.Combine will do exactly what you need without even complaining that you feed it such crappy (sorry, diverse) data.

And then you can decide how to handle the nulls, and how to add the source file name (do you actually need that?).  if you want to get fancy you can build a list of files first, then import the first file, then fetch the columns of all subsequent files and only import the "new" (not already present) columns.

Otherwise just do a few simple transforms.

let
    Source = Table.Combine({WW33,WW34,WW35}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Job"}, "Attribute", "Value"),
    #"Removed Duplicates" = Table.Distinct(#"Unpivoted Other Columns"),
    #"Pivoted Column" = Table.Pivot(#"Removed Duplicates", List.Distinct(#"Removed Duplicates"[Attribute]), "Attribute", "Value")
in
    #"Pivoted Column"

to arrive at this.

Hi Anonymous ,

I copied your 3 examples to 3 text files, W33.txt, W34.txt, and W35.txt and I get this output:

This should work to infiity if you continue to have that same rolling 3 week pattern. This will blow up though if you have multiple files with the same week numbers but different values. Week 33 in 2020 vs Week 33 in 2021 for example. The column names would need to be different, or a lot more logic would need to go into this.

That said, here is the M code:

let
    Source = Folder.Files("C:\Users\Ed Hansberry\OneDrive\Work Stuff\Power BI Forum Examples\Test Files\Weekly Data"),
    #"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
    #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Transform File"}),
    AllColumnsNames = 
        Table.ColumnNames(
            Table.Combine(#"Removed Other Columns1"[Transform File])
        ),
    #"Expanded Transform File" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", AllColumnsNames, AllColumnsNames),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Expanded Transform File", {"Job"}, "Column", "Amount"),
    #"Changed Type" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"Amount", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Job", "Column"}, {{"Amount", each List.Average([Amount]), type nullable number}}),
    #"Pivoted Column" = Table.Pivot(#"Grouped Rows", List.Distinct(#"Grouped Rows"[Column]), "Column", "Amount")
in
    #"Pivoted Column"

This also assumes when you do the combine operation, the "Transform Sample File" step only has the promote headers step, not the changed type step. That will wreck it too.

I am going to share my PBIX file here because you need to see the full thing including the combine files magic Power Query does. I edited the Transform Sample File query to suit my needs.

The key to this is the AllColumnNames step, which just keeps getting a larger and larger list of columns as the weeks roll on. I guess the Table.ColumnNames function does a unique union for me. I was expecting to have to wrap it in List.Distinct but it wasn't necessary. Those column names are what are used later when the table expands, vs the normal hardcoded columns you get when you manually expand a table column.

Also, my first table has a lot of duplicates. I get rid of those in the Grouped Rows. That step assumes the values are the same. So the Week 33 file for Job b in week 33 has 3. As long as the week 34 and 35 files also have 4 for job B in week 33, you are good. If that changes, then this will not work. I am using MIN() to get that number. MAX() and AVERAGE() will all do the same thing. If you are expecting that to change, MIN/MAX/AVERAGE woulda all return different results obviously, and none of them "4". That would seem to be to be a source data issue, and we'd have to discuss how to handle that scenario.

Here is my PBIX file and the 3 text files, in a single zip.

You'll need to unzip, then change the source in Power Query to point to where those TXT files are.

How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.