Combine power query output table with manually maintained column in one excel table

[Zdenek_Moravec]

I transform and load a CSV file into an excel sheet using Power Query:

Column1	Column2.1	Column2.2
01.01.2021	A	BC
01.02.2021	M	NO
01.03.2021	X	YZ

Customer wants to maintain next column of this table ("My Col") manually:

Column1	Column2.1	Column2.2	My col
01.01.2021	A	BC	A is first
01.02.2021	M	NO	M is second
01.03.2021	X	YZ	X is last

So far so good. The problem is that the next day, the CSV file can come in different order of records. And after Data-Update all, the table is updated in the this new order, but the manual column remains in the original order. Then we have data mismatch:

Column1	Column2.1	Column2.2	My col
01.02.2021	M	NO	A is first
01.03.2021	X	YZ	M is second
01.01.2021	A	BC	X is last

What is the correct process of combining power query table columns with manually maintained columns?
Thank You!

[#2 · Accepted solution]

@Zdenek_Moravec Not too long ago, similar questions came up on this forum. Both were responded to by 

@Sergei Baklan 

Thought the technique to do this would come in handy but didn't think it was very straight-forward. So, I created some kind of tutorial for myself, writing the process down step-by-step. And it includes a working, tough very simple, example. See attached.

[#3]

@Riny_van_Eekelen , I modified a bit, hope self-explainable

let
    Source  = Excel.CurrentWorkbook(){[Name="Source"]}[Content],
    Source3 = Excel.CurrentWorkbook(){[Name="Source3"]}[Content],
    SourceColumns   = Table.ColumnNames(Source),
    SourceColumns3  = Table.ColumnNames(Source3),
    FirstTime = List.ContainsAll(SourceColumns, SourceColumns3),
    Return = if FirstTime then Source else
        Table.Join(
            Source,  SourceColumns,
            Source3, SourceColumns
        )
in
    Return

Another table in your file.

[#4]

@Sergei Baklan Nice!!

[#5]

Hello @Riny_van_Eekelen and @Sergei Baklan 

Your solutions are already an Excel University, but it is understandable and it really works ;)

Thank You very much!

Zdenek Moravec

Cesky Krumlov, Czech Republic

[#6]

@Zdenek_Moravec , glad to help

[#7]

@Riny_van_Eekelen 

I am a bit of novice but am able to follow. In your example, both sources are existing tables. Is it possible to replicate this if the original source is a file in a folder? 

[#8]

@TdotnotbotThis relates to a rather old post and I believe it relates to connecting to one source and then adding information to it manually, and keep it synchronised with the original source data. It doesn't really matter where the original file sits. What exactly are you looking at?

[#9]

@Riny_van_Eekelen 

Below is a sample of the code following what I believe to be step-for-step as per instructions. When files are added and refreshed to the source folder, the manually updated rows remain in the same position in the workbook. Thank you for any help you might offer. This is driving me insane.

[#10]

@Tdotnotbot Difficult to diagnose without having a close look at the file. First, make sure that the added column is in fact part of the table you connect to in Source2. If it's just "hanging" on to the right-hand side without being part of the table it will certainly not work. 

The only other thing that comes to mind is that "SequenceNumber" in both tables are not aligned/unique. Are you sure that, for instance, an item with SequenceNumber 1 in Source2 is in fact the same item with that number in the table resulting from the #"Remove duplicates" step? 

Rather than working on big tables from combined files from a folder, can you get the technique to work on a single file? Easier to debug that way.

[#11]

@Sergei Baklan I didn't understand the use of the third table and how does it integrates with the rest of the solution...

[#12]

@FranzVerga 

Please ignore. That was attempt to use Table.Join() instead of Table.NestedJoin(). But the former not always returns correct result.

[#13]

@Zdenek_Moravec @Sergei Baklan 

In case if there is new data being added and same time we have to implement this method as well, Would it work?

[#14]

@Budhika 

Not sure I understood what do you mean exactly. If you add columns to the table returned by Power Query and your date have unique ID (or you may create it) - yest, it works.

[#15]

Hi @Riny_van_Eekelen, I am running into what I'm sure is a dumb mistake but I can't figure where I'm going wrong - I'm attempting this process but keep ending up with 3 tables instead of two when trying to follow your steps

1. "Create First Table (Source)" - Table 1

2. "Query it and load to...name (Source_2)" - Table 2

3. "Query table (Source_2), now including Notes" - Table 3

It seems that following the process I should only be ending up with two tables Source and Source_2, but again I keep ending up with 3 - Any help would be appreciated, thanks! 

[#16]

@acemats Difficult to help without seeing what you are doing. Can you share a link (Onedrive or similar) to you file?