Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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!
Solved! Go to 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.
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.
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!
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.
Load the CSV
add a custom column
Move the column to the beginning (first column)
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!
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.