Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Add an extra column from excel source

Hi,   I've tried som of the answer in here without succes.   In my Excel file, which is my data source, i've added an extra column. This extra colum I want to be a part of the dataset in BI. It d...
Need Help
  • MarcelBeug's avatar
    MarcelBeug
    8 years ago

    To be honest, I can't quite follow your steps, as these mention csv which is not applicable here.

     

    Looking at your query code, it seems to me that the issue is with:

     

    #"Power BI_Sheet" = #"Removed Other Columns"{[Item="Power BI",Kind="Sheet"]}[Data],

    as you have no "Power BI" sheet in the list of objects in your Excel workbook.

    Edit: this also corresponds with your error message.

     

     

    Shouldn't your code just be:

     

    #"Power BI_Sheet" = #"Removed Other Columns"{[Item="Power BI 2018",Kind="Sheet"]}[Data],

    Then you should see your additional colum.

     

    At #"Changed Type", you may want to adjust the column type of your new column, which is type any (ABC123) by default.

     

    Just some screen shot from another topic:

     

MarcelBeug's avatar
MarcelBeug
Community Champion
8 years ago

This is not an error that would appear in general, when adding a column to your Excel file.

 

So it must be something specific we are not able to determine without more relevant information from your side.

For example: query code, example data, at which step does the error occur (for the first tme).

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    New column = Kolonne

     

     

    The fail occur after I pres "Close and apply" in the query editor. I follow the written steps from the answer in another thread. 

     

    The tab i use for my visualization is Power BI 2018.

     

    Can the problem appear because i made calculated colums in BI where the new column would be?

    • MarcelBeug's avatar
      MarcelBeug
      Community Champion
      8 years ago

      Did you go through all query steps (just click each applied step one by one)? Most likely you would get the error at one of the steps already.

       

      Please go the Advanced Editor, select and copy the entire query code (you may take out any confidential information from the Source step), press "Insert code" in your reply here (see picture) and paste the copied code. OK.

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        Yes, I did go through all query steps. But still, I must have done something wrong.

         

        let
         Source = Excel.Workbook(File.Contents("C:\Users\Jonas\Desktop\Privatbudget.xlsx"), null, true),
         #"Removed Other Columns" = Table.SelectColumns(Source,{"Name", "Data", "Item", "Kind", "Hidden"}),
         #"Power BI_Sheet" = #"Removed Other Columns"{[Item="Power BI",Kind="Sheet"]}[Data],
         #"Promoted Headers" = Table.PromoteHeaders(#"Power BI_Sheet", [PromoteAllScalars=true]),
         #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Måned", type text}, {"Bruttoløn Jonas", Int64.Type}, {"Nettoløn Jonas", Int64.Type}, {"Overskydende skat/Særlige feriedage Jonas", Int64.Type}, {"Bruttoløn Signe", Int64.Type}, {"Nettoløn Signe", type number}, {"Overskydende skat/feriepenge Signe", Int64.Type}, {"Børnepenge", Int64.Type}, {"Mad", Int64.Type}, {"Opsparing - Oliver", Int64.Type}, {"Overført til lån", Int64.Type}, {"Netflix", Int64.Type}, {"HBO", Int64.Type}, {"Viaplay", Int64.Type}, {"Rengøring", Int64.Type}, {"Betalt bilregning inkl. Service", Int64.Type}, {"Yousee - overføres fra budget", Int64.Type}, {"El - overføres fra budget (1.050 kr)", Int64.Type}, {"Husleje", Int64.Type}, {"Angst-foreningen", type number}, {"A/C vand", Int64.Type}, {"Antenne", type number}, {"A conto varme", Int64.Type}, {"Gebyr for betaling af leje", Int64.Type}, {"Forsikringer", type number}, {"Licens", type number}, {"DJØF", Int64.Type}, {"TLF", Int64.Type}, {"MA", Int64.Type}, {"AKA", Int64.Type}, {"Jonas frisør", Int64.Type}, {"Vægtafgift", type number}, {"Ansvar + kasko", type number}, {"Bil diverse", Int64.Type}, {"FDM + vejhjælp", type number}, {"Yousee - trækkes fra lønkonto", Int64.Type}, {"EL - trækkes fra lønkonto", Int64.Type}})
        in
         #"Changed Type

         

        Step 6: Where I choose the colums I want to add, i just select them all. I can't see the colums, because its the sheets you choose under the tab "Choose colums". As said I only want to use the data from the tab "Power BI 2018" from my Excel source. And it is in this tab i made an extra colum.