Power query help to extract specific cells from rows to be demoted in transformation.

[Mohaaa]

Hi, I am looking for help in Power query extraction, where in Row 1 the CSV file has specific values in the cells B1 (Date), C1 (Time) & D1 (Date) associated with the file. Proper dataset starts from Row 2 which has 64 columns. I would require the cells B1, C1 & D1 to retrieve as additional 3 columns to the dataset. Post which we will promote the row 2 as headers of the dataset and use these 3 fields to the dataset. We have attached 2 files as below:

1. Format of Dataset attached here
2. Image of expected results from Power query extraction

 

[GraH - Guido]

This can work.

let
    Source = Csv.Document(File.Contents("K:\uploads\PQ Test1_Data format.csv"),[Delimiter=";", Columns=64, Encoding=65001, QuoteStyle=QuoteStyle.None]),
    Fst_row = List.LastN(List.FirstN(Table.ToColumns(Table.FirstN(Source,1)),4),3),
    Table = Table.ToColumns(Table.Skip(Source,1))&Fst_row,
    Combine = Table.FromColumns( Table )
in
    Combine

 

[Mohaaa]

This can work.

let
    Source = Csv.Document(File.Contents("K:\uploads\PQ Test1_Data format.csv"),[Delimiter=";", Columns=64, Encoding=65001, QuoteStyle=QuoteStyle.None]),
    Fst_row = List.LastN(List.FirstN(Table.ToColumns(Table.FirstN(Source,1)),4),3),
    Table = Table.ToColumns(Table.Skip(Source,1))&Fst_row,
    Combine = Table.FromColumns( Table )
in
    Combine

Hi Grah -Thanks for your code. However I use the 30 days of CSV files generated in a folder, so I have amended the query as below and would need your help to extract from check all files and create these 3 cells (B1, C1 & D1) as separate columns to the data set inside each file which already has 64 columns. So if I wanted to retrieve 67 columns (64 + 3) in total from each file.

My Code:
//Source = Folder.Files("T:\MI & Control\File Uploads\IM Custodian\BO\NY\2024\03 March"),
Fst_row = List.LastN(List.FirstN(Table.ToColumns(Table.FirstN(Source,1)),4),3),
Table = Table.ToColumns(Table.Skip(Source,1))&Fst_row,
Combine = Table.FromColumns( Table )

 

[GraH - Guido]

Mohaaa​

Glad to read the code was helping you.

If you read from folder, then there is a function query on a sample file. At least if you followed the UI steps to build it.
Update that code with the code I gave you. Apply this function on the list of binaries.
Combine all files.

 

[Mohaaa]

Mohaaa​

Glad to read the code was helping you.

If you read from folder, then there is a function query on a sample file. At least if you followed the UI steps to build it.
Update that code with the code I gave you. Apply this function on the list of binaries.
Combine all files.

@GraH - Guido - I tried using Folder.Files but unable to crack. Appreciate your help.

 

[GraH - Guido]

@Mohaaa ,
You might have noticed I'm not so active anymore on the forum.
Please provide your query attempt. The screenshot is kind of pointless and useless tbh.