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  
    
Customer IDSurnameFirst NameBalance
1Smith,James£3283.43
2RobsonEdward£8794.20
3MorrisJonathon£13863.50

 

but want to convert it into something like this

 

Customer IDSurnameFirst NameBalanceDate
1Smith,James£3283.4317-Aug-2023
2RobsonEdward£8794.2017-Aug-2023
3MorrisJonathon£13863.5017-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

Need Help

4 Replies

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