Add column to data set using append

[croufspesi]

I have a pbix set to append files from within a folder. Folder currently contains 3 files

2018 Revenue

2019 Revenue

2020 Revenue

I have added 2 additional columns to the end of the data set in only the 2020 Revenue file and cannot get them to load.

- Refresh does not bring them in.

- Choose columns in query editor does not display them.

- Other strings in here recommended adjusting the # of columns within the Advanced Editor, but I cannot find a reference to a number of columns within the code in my file.

Can I add columns to just one of the files that is appending?  Or do added columns need to be added to all 3 of my files?

[amitchandak]

@croufspesi , Open your data in data transformation and right-click on the table name and open advance editor

You will see the script

let
    Source = Excel.Workbook(File.Contents("..\salesAfter regdate.xlsx"), null, true),
    sales_Sheet = Source{[Item="sales",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(sales_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Sales_Rep", type text}, {"Sales_Date", type date}, {"Units", Int64.Type}})
in
    #"Changed Type"

Add columns and datatype to check

[Ashish_Mathur]

Hi,

If you will not be adding columns regularly, then go to the Query Editor > View > Advanced Editor and edit the line there which has the column names to include the 2 new columns that you added.

Hope this helps.

[croufspesi]

@Ashish_Mathur 

I do not see any of my current column names within the code.  

let
Source = Folder.Files("F:\E-Marketing\Numbers\PowerBI\Annual Sales Data - Copy"),
#"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))),

[Ashish_Mathur]

Hi,

Follow the technique shown in this video.

Hope this helps.

[edhans]

When you combine files, it takes a sample file that you pick, usually the first file. If that file doesn't have that column, it won't bring it in by default.

You can fix that though. Honestly, unless you are pretty good with Power Query, it might be easire to redo the Combine operation and pick the 3rd file vs the 1st file as the sample.

You can do it manually though by changing the index number of the sample file shown below. Change the {0} to {2} (it indexes at zero, then look at the Transform Sample File operation, and the other steps it created in your main query so it doesn't wind up removing the column after it brings it in.

[croufspesi]

@edhans 

EDIT: I got it!

at first, I tried to edit to the 2 within the code bar - that resulted in errors.

but when I opened that step in the advanced editor and changed it to the 2, it all fell in line perfectly.  

thanks thanks!!

[edhans]

Excellent @croufspesi - glad you got it sorted out.

[croufspesi]

@edhans 

When changing the 0 to a 2, I get an error:  Expression.Error: We cannot convert a value of type Table to type Binary.