Forum Discussion

Mal_Franks's avatar
Mal_Franks
New Member
3 years ago

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

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  ...
Need Help
smozgur's avatar
smozgur
Helper I
3 years ago

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's avatar
Mal_Franks
New Member
3 years ago

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"