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.