combine csv files with inconsistent columns and missing columns

Start as usual to import all files, e.g. from a folder, click Combine and combine and transform the data. This way PQ creates the necessary functions and queries for you.

There is one query to transform the example file, in there we have to perform some steps. Select the first 3 columns, right-click and unpivot other columns, you'll get an Attribute and Value column. In the Attribute column you'll see all the different headers with the index numbers in brackets, e.g. "b343055e href (2)" so the first step is to remove these.

In the Transform tab choose Extract, Text before Delimiter and use the "(" as delimter. We know there's a blank after the content so Trim the column also.

Basically the next step would be to pivot the Attribute column, but that doesn't work directly, if you try you'll get an error "Expression.Error: There were too many elements in the enumeration to complete the operation."

The solution is to group and add a local index.

Select the first 4 columns, group and use "All Rows" and column and choose "All Rows" as aggregation and you get this code:

= Table.Group(#"Trimmed Text", {"a263ff10 href", "d6d4b13f", "f397a0c8", "Attribute"}, {{"All Rows", each _, type table [a263ff10 href=nullable text, d6d4b13f=nullable text, f397a0c8=nullable text, Attribute=text, Value=text]}})

Modify the code to this code:
= Table.Group(#"Trimmed Text", {"a263ff10 href", "d6d4b13f", "f397a0c8", "Attribute"}, {{"All Rows", each Table.AddIndexColumn(_, "Index")}})

Remove all columns, except the "All Rows" column, expand the column, the data is the same as before, just an Index column is added ith an index number of each group.

Now you can Pivot the Attribute column and use the Value column as value and choose "Dont' aggregate" as function.

The final step is to remove the Index column, done. Below is the code how to transform the example file.

Andreas.

let
    Source = Csv.Document(Parameter1,[Delimiter=",", Columns=51, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Promoted Headers", {"a263ff10 href", "d6d4b13f", "f397a0c8"}, "Attribute", "Value"),
    #"Extracted Text Before Delimiter" = Table.TransformColumns(#"Unpivoted Other Columns", {{"Attribute", each Text.BeforeDelimiter(_, "("), type text}}),
    #"Trimmed Text" = Table.TransformColumns(#"Extracted Text Before Delimiter",{{"Attribute", Text.Trim, type text}}),
    #"Grouped Rows" = Table.Group(#"Trimmed Text", {"a263ff10 href", "d6d4b13f", "f397a0c8", "Attribute"}, {{"All Rows", each Table.AddIndexColumn(_, "Index")}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Grouped Rows",{"All Rows"}),
    #"Expanded {0}" = Table.ExpandTableColumn(#"Removed Other Columns", "All Rows", {"a263ff10 href", "d6d4b13f", "f397a0c8", "Attribute", "Value", "Index"}, {"a263ff10 href", "d6d4b13f", "f397a0c8", "Attribute", "Value", "Index"}),
    #"Pivoted Column" = Table.Pivot(#"Expanded {0}", List.Distinct(#"Expanded {0}"[Attribute]), "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Index"})
in
    #"Removed Columns"