Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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 doesn't help to press "Refresh". 

 

I've tried an answer in here, which was:

 

"1) refresh everything, make sure the new column is in the source csv

2) under applied steps in query editor, go to source and hit the settings gear icon to right of "source"

3) Choose Open file as "Automatic" hit OK

4) Your table will dissappear, double click the csv file icon, hit "insert" when the insert step box pops up

5) Your newly added column will appear at step 1 "source"

6) to make sure it stays , under "choose columns" or if you have a "removed columns" ...make sure the check box for that new column is checked

7) Your column is good to go now"

 

That didn't work either. I got this fail-message, when i applied the changes: Expression.Error: The key didn't match any rows in the table.

 

Is there anyone, who can help?

 

Regards Jonas

 

 

1 ACCEPTED SOLUTION

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:

 

Change data type.png

Specializing in Power Query Formula Language (M)

View solution in original post

6 REPLIES 6
MarcelBeug
Community Champion
Community Champion

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).

Specializing in Power Query Formula Language (M)
Anonymous
Not applicable

New column = Kolonne

 

Ny kolonne.PNGQuery.PNG

 

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?

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.

 

Insert Query code in Power BI Community forum.png

Specializing in Power Query Formula Language (M)
Anonymous
Not applicable

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.

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:

 

Change data type.png

Specializing in Power Query Formula Language (M)
Anonymous
Not applicable

Hi Marcel,

 

I can only say, that i'm very impressed by your BI skills! It works perfectly.

 

Thank you for your time.

 

Kind regards Jonas

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.