Unpivoting multiple columns

Actually,

thinking about it the formula in step 4 should be =Number.Mod(Number.IntegerDivide([Index],25),7)+1 so that it cycles from 1 to 7 for each record

I know nothing about API, data factory, Azure Blobs, Azure tables or modifiers.

A phrase like "a transaction with 4 items and 2,0,0 and 1 modifier applied to the items" is complete abacadabra to me.

What I would expect from your description, is 2 fields - TransactionID and DateTime - plus a mulitple of 7 fields, each with the same name except for a number that identifies each group of 7 fields.

Instead, in your example there are not 2 but 109 (!) fields without digits in the field name.

So I won't be able to help you.

Maybe if you come up with a far better explanation and realistic examples of the required transformations.

For instance, how should the result look like, for all fields with number 1 in the column name:

Well, it may be a heavy topic, but it is still fascinating and I made some progress.

There are some points of attention left, especially: duplicated column names in the input.

First, I replaced all NULL's with null in the Sheet1 query which I named query Sheet1_NULL_To_null. I hope this is OK as it cleans up a bit. The code (with my file path) is now:

let
    Source = Excel.Workbook(File.Contents("C:\Users\Marcel\Documents\Forum bijdragen\Power BI Community\Elliot 20170609\unpivotdatastructure.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Replaced Value" = Table.ReplaceValue(#"Promoted Headers","NULL",null,Replacer.ReplaceValue,Table.ColumnNames(#"Promoted Headers"))
in
    #"Replaced Value"

I also removed the #"Changed Type" step as this will give errors if columns will change (in fact it already gave an error).

Second, I unpivoted all except the first 23 columns in query Unpivot:

let
    Source = Sheet1_NULL_To_null,
    First23columnNames = List.FirstN(Table.ColumnNames(Source),23),
    Unpivot = Table.UnpivotOtherColumns(Source, First23columnNames, "Attribute", "Value")
in
    Unpivot

Third, I created a query FixedParts that returns all fixed parts in the column names:

List.Transform({1..7}, each "Modifier"&Text.From(_))&{
        "ItemName",
        "Item Name",
        "Notes",
        "Quantity",
        "Category",
        "Sku",
        "ItemID",
        "VariationID",
        "Sales",
        "DiscountAmount",
        "Discount",
        "Name",
        "Amount",
        "ID",
        "list_itemizations_itemization_type"}

Next, I created a query Pivot:, that splits the "Attribute" column in a fixed part and a variable part.

This is quite an interesting query bcause of the way Splitter functions are used to split the fixed parts and the variable parts of the column names.

Example column name "ItemName1Quantity" is split on any delimiter (which are the fixed parts), which includes "ItemName" and "Quantity", so this leaves "1" (as the delimiters are not part of the result after splitting). Next, the column name is split again on each delimiter, with the variable parts (the "1" in this example) as delimiter, so this will take out the "1", leaving "ItemName" and "Quantity" which are then combined to "ItemNameQuantity" as fixed column name..

And the query is also interesting because it produces the end result. 

let
    Source = Unpivot,
    VariableParts = Table.AddColumn(Source, "VariableParts", each List.Select(Splitter.SplitTextByAnyDelimiter(FixedParts)([Attribute]), each _ <> "")),
    #"Added Custom" = Table.AddColumn(VariableParts, "FixedPart", each Text.Combine(List.Select(Splitter.SplitTextByEachDelimiter([VariableParts])([Attribute]), each _ <> ""))),
    #"Trimmed Text" = Table.TransformColumns(#"Added Custom",{{"VariableParts", Text.Combine, type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Trimmed Text",{"Attribute"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[FixedPart]), "FixedPart", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Pivoted Column", each [VariableParts] >= "1" and [VariableParts] <= "7")
in
    #"Filtered Rows"

Actually, the variable part should only contain the numbers 1-7, but the source data has duplicate column names:

Some fields for Item1 are duplicated, some of them in this picture (but there are more duplicates):

These fields are automatically removed during the unpivot step, because of the NULL values that I replaced with null.

But field list_itemizations_itemization_type appears 7 times. I think these should have had group numbers 1-7:

Consequently, these fields were given suffixes ("_1", "_2" etcetera) during the #"Promoted Headers" step in the very first query.

This is how it looks like, just before the last step in the last query (query Pivot):

Next steps for you to try and copy the queries above to your environment, so you can follow all the steps, then clean up your input data, see if it all works and provide feedback.