Add a filterable and sortable blank column in a table resulting of a query

%3CLINGO-SUB%20id%3D%22lingo-sub-1981874%22%20slang%3D%22en-US%22%3EAdd%20a%20filterable%20and%20sortable%20blank%20column%20in%20a%20table%20resulting%20of%20a%20query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1981874%22%20slang%3D%22en-US%22%3E%3CP%3EI%20need%20to%20add%20a%20blank%20column%20in%20the%20table%20resulting%20of%20a%20query%20that%20will%20allow%20information%20to%20be%20entered%2C%20sorted%20and%20filtered.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhen%20I%20add%20the%20column%2C%20when%20I%20sort%2C%20the%20information%20does%20not%20follow%20the%20row%20it%20was%20entered%20in.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20tried%20the%20instruction%20at%20this%20link%20but%20without%20success%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fwww.mrexcel.com%2Fboard%2Fthreads%2Fproblem-with-manually-added-column-in-a-table-connected-to-a-query.1143794%2F%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3EProblem%20with%20manually%20added%20column%20in%20a%20table%20connected%20to%20a%20query%20%7C%20MrExcel%20Message%20Board%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20you%20help%20me%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1981874%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EPower%20BI%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
New Contributor

I need to add a blank column in the table resulting of a query that will allow information to be entered, sorted and filtered. 

 

When I add the column, when I sort, the information does not follow the row it was entered in. 

 

I tried the instruction at this link but without success Problem with manually added column in a table connected to a query | MrExcel Message Board

 

Can you help me?

1 Reply

@Martine Lefebvre 

Table returned by Power Query is not in sync with manually added column. However, you may sync if you have ID for your records or any unique field. Generate table by Power Query first time, add manual column, query resulting table. Merge initial query with this one by ID and expand manual column. Now you shall to combine that into one initial query. Refresh, if extra column appears remove it. Resulting script shall be like

let
    Source = Excel.CurrentWorkbook(){[Name="Source"]}[Content],
    Source2 = Excel.CurrentWorkbook(){[Name="Source_2"]}[Content],
    #"Merged Queries" = Table.NestedJoin(Source, {"ID"}, Source2, {"ID"}, "Source2", JoinKind.LeftOuter),
    #"Expanded Source2" = Table.ExpandTableColumn(#"Merged Queries", "Source2", {"Note"}, {"Note"})
in
    #"Expanded Source2"

Please see attached sample.