Struggling loading mutilpe text files into BI

PeterDWP

I noticed that there are 16 rows in each log. We can add an index column start from 10000 and with 625 increment in Query Editor.

Then divide by 10000 and round down for this column in Transform tab.

Now the table will like below.

Select column1 and pivot it with following settings.

At last, delete the first Index column.

Below is the completed Power Query which can be pasted into Advanced Editor.

let
    Source = Excel.Workbook(File.Contents("C:\head.xlsx"), null, true),
    head_Sheet = Source{[Item="head",Kind="Sheet"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(head_Sheet,{{"Column1", type text}, {"Column2", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type"),
    #"Added Index" = Table.AddIndexColumn(#"Promoted Headers", "Index", 10000, 625),
    #"Divided Column" = Table.TransformColumns(#"Added Index", {{"Index", each _ / 10000, type number}}),
    #"Rounded Down" = Table.TransformColumns(#"Divided Column",{{"Index", Number.RoundDown, Int64.Type}}),
    #"Pivoted Column" = Table.Pivot(#"Rounded Down", List.Distinct(#"Rounded Down"[Column1]), "Column1", "Column2"),
    #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Index"})
in
    #"Removed Columns"

Best Regards,

Herbert