Repeat a single "misplaced" field content along a column

[WilliamAzevedo]

Hello.

I have a .csv dataset I need to transform in an in Excel file (later in a PowerBI report, if I'm able to do it), but there's a single field "out" of the "main" table which i need to be part of it, repeated along the table.

Here's a sample of the data:

txtReportTitle

Detalhe de Viagem de Motorista

textbox2,User_Description_1,textbox12,LocalTimestampRange_1,textbox16,Driver_Description_1,textbox18,Driver_IdentityNumber

Publicado por:,Publisher,Período de datas:,2024-03-01 00:00 .. 2024-03-31 23:59,Descrição do motorista:,DRIVER    ,Número de identificação:,01685071538

textbox1,Trip_StartLocalTimestamp_1,textbox4,Trip_EndLocalTimestamp_1,Distance_Abbreviation,Trip_Distance_1,textbox10,Trip_Duration_Description_1,textbox3,Vehicle_Description,textbox5,textbox6,textbox7,Event_LocalTimestamp_1,EventType_Description,Location_Description_1

Início Hora:,2024-03-17 09:26:40,Final Hora:,2024-03-17 09:48:29,Distance (km):,2.81,Duração:,00:21:49,Veículo:,GIM0I04 - GOL 1.0 MPI FLEX 4P,Data e Hora,Descrição do evento,Nome do Local,2024-03-17 09:26:40,Ignição ligada,"Conceição da Feira, Conceição da Feira, Região Geográfica Imediata de Feira de Santana, BRASIL"

Início Hora:,2024-03-17 09:26:40,Final Hora:,2024-03-17 09:48:29,Distance (km):,2.81,Duração:,00:21:49,Veículo:,GIM0I04 - GOL 1.0 MPI FLEX 4P,Data e Hora,Descrição do evento,Nome do Local,2024-03-17 09:26:40,Posição regular,"Conceição da Feira, Conceição da Feira, Região Geográfica Imediata de Feira de Santana, BRASIL"

Início Hora:,2024-03-17 09:26:40,Final Hora:,2024-03-17 09:48:29,Distance (km):,2.81,Duração:,00:21:49,Veículo:,GIM0I04 - GOL 1.0 MPI FLEX 4P,Data e Hora,Descrição do evento,Nome do Local,2024-03-17 09:26:45,Posição (parado),"Conceição da Feira, Conceição da Feira, Região Geográfica Imediata de Feira de Santana, BRASIL"

I need the field DRIVER (highlighted in red) to be repeated in a column along this table, like this:

Motorista

Driver

Início Hora:

2024-03-17 09:26:40

Final Hora:

2024-03-17 09:48:29

Distance (km):

2.81

Duração:

00:21:49

Veículo:

GIM0I04 - GOL 1.0 MPI FLEX 4P

Data e Hora

Descrição do evento

Nome do Local

2024-03-17 09:26:40

Ignição ligada

Conceição da Feira, Conceição da Feira, Região Geográfica Imediata de Feira de Santana, BRASIL

2024-03-17

Driver

Início Hora:

2024-03-17 09:26:40

Final Hora:

2024-03-17 09:48:29

Distance (km):

2.81

Duração:

00:21:49

Veículo:

GIM0I04 - GOL 1.0 MPI FLEX 4P

Data e Hora

Descrição do evento

Nome do Local

2024-03-17 09:26:40

Posição regular

Conceição da Feira, Conceição da Feira, Região Geográfica Imediata de Feira de Santana, BRASIL

2024-03-17

Driver

Início Hora:

2024-03-17 09:26:40

Final Hora:

2024-03-17 09:48:29

Distance (km):

2.81

Duração:

00:21:49

Veículo:

GIM0I04 - GOL 1.0 MPI FLEX 4P

Data e Hora

Descrição do evento

Nome do Local

2024-03-17 09:26:45

Posição (parado)

Conceição da Feira, Conceição da Feira, Região Geográfica Imediata de Feira de Santana, BRASIL

2024-03-17

Is it possible? Thank you in advance!

[MattAllington · Accepted solution]

There is no simple "just do this..." generic instruction, it depends on your data.  The article shows how to extract a call and turn it into a column. Based on your description, it sounds like each file has it's own cell. If that cell is in the same location in each file, then it should be straight forward.

Read my article about file combine here https://exceleratorbi.com.au/understanding-power-query-combine/

So if my assumptions are correct, you need to first do file combine, then modify the sample query file to extract the cell value (From each file) into a column, then use the combine query to load it all up.

[WilliamAzevedo]

Your assumption is correct, I read the article about file combine, reread the article about converting a cell value into a column and if I hadn't done it myself, I wouldn't have believed it just worked.

I can't thank you enough, but, thank you again!

[WilliamAzevedo]

I found the link and it worked nicely with one file (thank you very much for the post, Matt!): Convert a Cell Value into a Column with Power Query - Excelerator BI

But I didn't understand how to apply it in a folder with files where the field "Driver" changes from file to file. Simply putting the files there repeated the data from the first file.

[MattAllington]

Load the CSV 

add a custom column

Move the column to the beginning (first column)

[WilliamAzevedo]

1. Hi, Matt!

And how do I move the data from that field to the custom column? Could you explain, please?

Edit:

And I'm sorry for bothering you, but I saw the link to your book and it made me remember that I saw exactly what I've just asked you in your blog! I created this post because I was dumb to not bookmark it and I just don't remember the link. I'll try to search it there!

Thank you in advance!