Struggling loading mutilpe text files into BI

[PeterDWP]

Hi all I'm struggling here I'm new to BI but I have 800 ish text files, (in the current sample) all called head.txt each in a different folder, each file looks like this

Mode CFA drilling

start date 2016-06-24

start time 11:22:48

stop date 2016-06-24

stop time 13:27:00

job site 15D795

pile number 160A

maximum depth [cm] 1608

style 1

pile diameter [cm] 60

total concrete vol. [m³] 6.1225

no. imp. (concrete pump) 155

liter/imp. (concrete pump) 39.5

auger length [cm] 100

concrete vol. [m³] 5.727

over consumption [%] 26.0

I can get the data to load into two columns but the first column just repeats the labels, I wish to transpose this into a table with 16 columns i.e. mode ..to.. over consumption, what I get is a single row with no consolidation

Any ideas??

[v-haibl-msft]

@PeterDWP

Just to confirm, you can already load these text files into Power BI like following left table and you want to convert it to the right one?

Best Regards,

Herbert

[PeterDWP]

Yes thats correct, ive 800 plus text files all in indevidual folders and BI worked a treat loading its just this final tranposition I need to solve

P

[v-haibl-msft · Accepted solution]

@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