Putting value from a specific cell in a Excel spreadsheet into Column

[Mal_Franks]

Hi

I'm extremely new to the wonderful world of Power Bi and I have the following query

I download a report something like this

Report Name:	Cash Balance
Date:	17-Aug-2023
Customer ID	Surname	First Name	Balance
1	Smith,	James	£3283.43
2	Robson	Edward	£8794.20
3	Morris	Jonathon	£13863.50

but want to convert it into something like this

Customer ID	Surname	First Name	Balance	Date
1	Smith,	James	£3283.43	17-Aug-2023
2	Robson	Edward	£8794.20	17-Aug-2023
3	Morris	Jonathon	£13863.50	17-Aug-2023

Got as far as 

= Table.AddColumn(#"Changed Type", "Custom", each if [Column1] = "Customer ID" then "Date" else "17-Aug-2023", type text)

but I need the "17-Aug-2023" to be the actual date in the worksheet before I instruct it to remove the first three rows and tell it to use the new first row as headers.

Any ideas how I do this?

Thanks

[smozgur]

Try the following code, change the sample binary source with yours:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VY5BDoIwEEWv0nQNBFoUdKegiSS6wCVhUaWxJNKatsQDcRNO5rQGExdN///zJn+aBtf8pbRFFzbwLQ5wwYxAe/Zk8s7BIv/aoMElsx5IsnA3PkISE/o3Rz+3JMVorBq4RqcSsuuoJXSAOvbafAvBLFVuIXHY0FsRgKhgbuCfJ0pyGqXUIwSSWt2MkiAO3ZvpzjN5tkkjEnvG3XVWWvduvVKSWeHxeUpovqbRCrD2Aw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]),
    
    Date = Source{1}[Column2],
    RemoveRows = Table.Skip(Source,3),
    PromoteHeaders = Table.PromoteHeaders(RemoveRows, [PromoteAllScalars=true]),
    AddDateColumn = Table.AddColumn(PromoteHeaders, "Date", each Date)
in
    AddDateColumn

Date = Source{1}[Column2] is the line that extracts the date value before from the source data.  

[Mal_Franks]

Hi

Here's the new code. I think I'm not getting something as the Source{1}[Column2] line doesn't seem to put any value into my variable FDate

let
    Source = Excel.Workbook(File.Contents("C:\Users\malcolm.franks\Downloads\feed\clientsbalance.xls"), null, true),
    #"New Sheet1" = Source{[Name="New Sheet"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(#"New Sheet1",{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}}),
    #"Removed Top Rows" = Table.Skip(#"Changed Type",3),
    #"FDate" = Source{1}[Column2],
    #"Removed Blank Rows" = Table.SelectRows(#"Removed Top Rows", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
    #"Removed Top Rows1" = Table.Skip(#"Removed Blank Rows",3),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows1", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Customer ID", type text}, {"Surname", type text}, {"First Name", type text}, {"Balance", type number}}),
    #"Added Report Date" = Table.AddColumn(#"Changed Type1", "Report Date", each FDate)
in
    #"Added Report Date"

[Mal_Franks]

Thanks for the assistance. As I said I'm very new to this so a bit confused by the binary source line where do I find this information?

[smozgur]

Sorry for the confusion. I used sample data, so that's why the "Source =" line in the code shows the binary data string. You just need to replace your "Source =" line with the existing one in the code.

The existing code line:

Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VY5BDoIwEEWv0nQNBFoUdKegiSS6wCVhUaWxJNKatsQDcRNO5rQGExdN///zJn+aBtf8pbRFFzbwLQ5wwYxAe/Zk8s7BIv/aoMElsx5IsnA3PkISE/o3Rz+3JMVorBq4RqcSsuuoJXSAOvbafAvBLFVuIXHY0FsRgKhgbuCfJ0pyGqXUIwSSWt2MkiAO3ZvpzjN5tkkjEnvG3XVWWvduvVKSWeHxeUpovqbRCrD2Aw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]),

If you look at your own code, then you should also see a Source line. Just copy and replace it with the existing one above.

If you like, just copy and paste your existing code (entire code please), then we can make the replacement for you and post the modified code.