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

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

      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.

       

  • 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"