Need help to extract specific cells from rows to be demoted as fields

[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. Image of expected results  from from Power query extraction

[dufoq3 · Accepted solution]

Hi @Mohaaa, check this:

Before

After

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcvNwCVLSUVIAA9eKkqLE5BIF56LUxJLM/DwFl8SSVCsFAyN9I0t9IwMjE9wqQzJzgSqNDK0MLIGKnP2dULTF6kQrOefnlObmKRiCpCFMIwTTGMGEqC7Oz01VSEksSQSph3OMkDnGyBygrlgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Column 1" = _t, #"Column 2" = _t, #"Column 3" = _t, #"Column 4" = _t]),
    Row1Extract = List.Transform(List.Skip(Record.ToList(Source{0})), Text.Trim),
    Combined = Table.FromColumns(Table.ToColumns(Table.Skip(Source, 1)) & Table.ToColumns(Table.FromRows({Row1Extract})))
in
    Combined

[Anonymous]

Hi @Mohaaa ,
You can try the following code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs7PKc3NUzBU0oExjRBMYwTTRCk2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [FHDR = _t, #"Extract Creation Date: 02/29/2024" = _t, #"Extract Creation Time: 21:09" = _t, #"COB: 02/29/2024" = _t]),
    ColumnNames = List.Skip(Table.ColumnNames(Source), 1),
    ColumnNamesToRepeat = List.FirstN(ColumnNames, 3),
    ColumnsToRepeat = Table.SelectColumns(Source, ColumnNamesToRepeat),
    RenamedColumnsToRepeat = Table.RenameColumns(ColumnsToRepeat, List.Zip({ColumnNamesToRepeat, List.Transform(ColumnNamesToRepeat, each _ & " (Copy)")})),
    CombinedTable = Table.FromColumns(
        Table.ToColumns(Source) & Table.ToColumns(RenamedColumnsToRepeat),
        Table.ColumnNames(Source) & List.Transform(ColumnNamesToRepeat, each _ & " (Copy)")
    ),
    #"Replaced Value" = Table.ReplaceValue(CombinedTable,"Column 2","Column 65",Replacer.ReplaceText,{"Extract Creation Date: 02/29/2024 (Copy)"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","Column 3","Column 66",Replacer.ReplaceText,{"Extract Creation Time: 21:09 (Copy)"}),
    #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","Column 4","Column 67",Replacer.ReplaceText,{"COB: 02/29/2024 (Copy)"})
in
    #"Replaced Value2"

Best regards,
Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Mohaaa]

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

My Code: 

let
Source = Folder.Files("T:\MI & Control\File Uploads\IM Custodian File\BONY\2024\03 March"),
Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45wc3PNYzDIz0PMyBUOExjRBWYlTRCKzfGA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"HDR" = _t, #"Extract Creation Date: 02/29/2024" = _t, #"Extract Creation Time: 21:09" = _t, #"COB: 02/29/2024" = _t]),
columnNames = List.Skip(Table.ColumnNames(Source), 1),
ColumnNamesToRepeat = List.FirstN(columnNames, 3),
ColumnsToRepeat = Table.SelectColumns(Source, ColumnNamesToRepeat),
RenamedColumnsToRepeat = Table.RenameColumns(ColumnsToRepeat, List.Zip({ColumnNamesToRepeat, List.Transform(ColumnNamesToRepeat, each _ & " (Copy)")})),
CombinedTable = Table.FromColumns(
Table.ToColumns(Source) & Table.ToColumns(RenamedColumnsToRepeat),
Table.ColumnNames(Source) & List.Transform(ColumnNamesToRepeat, each _ & " (Copy)")),
#"Replaced Value" = Table.ReplaceValue(CombinedTable, "column 1", Table.Replacer.ReplaceText, {"Extract Creation Date: 02/29/2024 (Copy)"}),
#"Replaced Value2" = Table.ReplaceValue(#"Replaced Value", "column 3", Table.Replacer.ReplaceText, {"Extract Creation Time: 21:09 (Copy)"}),
#"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2", "column 4", Table.Replacer.ReplaceText, {"COB: 02/29/2024 (Copy)"})
in
#"Replaced Value3"

[Mohaaa]

@Greg_Deckler  - Pls find the data pasted as text below:

FHDR Extract Creation Date: 02/29/2024 Extract Creation Time: 21:09 COB: 02/29/2024 Extract Creation Date: 02/29/2024 Extract Creation Time: 21:09 COB: 02/29/2024
Column 1 Column 2 Column 3 Column 4 Column 5 - - - Column 64 Column 65 Column 66 Column 67

[Omid_Motamedise]

To achieve this in Power Query, you can follow these steps:

Import the CSV file: Load the CSV file into Power Query.

Retrieve values from B1, C1, and D1:

First, you can refer to Row 1 using the Table.FirstN function.
Then, you can extract the values from columns B, C, and D.
Add the extracted values as new columns:

Use the Table.AddColumn function to add the values of B1, C1, and D1 as new columns to your dataset.
Remove Row 1 and promote Row 2 to headers:

Remove Row 1 since the data starts from Row 2.
Promote Row 2 as headers using the Table.PromoteHeaders function.

let
    // Load the data
    Source = Csv.Document(File.Contents("YourFilePath.csv"), [Delimiter=",", Columns=64, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    
    // Extract values from Row 1
    FirstRow = Table.FirstN(Source, 1),
    Date1 = FirstRow{0}[Column2],  // B1
    Time = FirstRow{0}[Column3],   // C1
    Date2 = FirstRow{0}[Column4],  // D1
    
    // Skip the first row and start from the actual data (Row 2 onwards)
    DataRows = Table.Skip(Source, 1),
    
    // Promote the second row to headers
    PromotedHeaders = Table.PromoteHeaders(DataRows, [PromoteAllScalars=true]),
    
    // Add the extracted columns
    AddDate1 = Table.AddColumn(PromotedHeaders, "Date1", each Date1),
    AddTime = Table.AddColumn(AddDate1, "Time", each Time),
    AddDate2 = Table.AddColumn(AddTime, "Date2", each Date2)
in
    AddDate2

[Mohaaa]

Hi Omid- Thanks for your help. However, I amended the Source in the code accordingly as below but noticed this error.  Please advise.

Error I noticed:

My Code

let
Source = Folder.Files(("T:\\MI & Control\\File Uploads\\IM Custodian File\\BONY\\2024\\03 March")),

// Extract values from Row 1
FirstRow = Table.FirstN(Source, 1),
Date1 = FirstRow{0}[Column2], // B1
Time = FirstRow{0}[Column3], // C1
Date2 = FirstRow{0}[Column4], // D1

// Skip the first row and start from the actual data (Row 2 onwards)
DataRows = Table.Skip(Source, 1),

// Promote the second row to headers
PromotedHeaders = Table.PromoteHeaders(DataRows, [PromoteAllScalars=true]),

// Add the extracted columns
AddDate1 = Table.AddColumn(PromotedHeaders, "Date1", each Date1),
AddTime = Table.AddColumn(AddDate1, "Time", each Time),
AddDate2 = Table.AddColumn(AddTime, "Date2", each Date2)
in
AddDate2

[Mohaaa]

I have the sample file format, unfortunate no option to upload Excel/CSV files. So pasting them here.

FHDR	Extract Creation Date: 02/29/2024	Extract Creation Time: 21:09	COB: 02/29/2024						Extract Creation Date: 02/29/2024	Extract Creation Time: 21:09	COB: 02/29/2024
Column 1	Column 2	Column 3	Column 4	Column 5	-	-	-	Column 64	Column 65	Column 66	Column 67

[Greg_Deckler]

@Mohaaa Can you post same data as text?