Append queries (Automation)

[gtamir]

Hi. I have a general question about appending tables and by that to automate procedures.

I have Sales table from April. There are the useal columns + a column that I created by merging two columns.

I first duplicated two columns and then merged tham so keeping the original files.

Now I have an extra column.

I want to append an exact same file from May. It doesn't of cours have this extra column.

In the appended file (Sales) the data in the extra column for may in NULL. It didn't merge the data of the two columns.

Well, the two colums are those I duplicated and they don't exist. 

How can I do that?

Maybe if I could merge the columns and keep the originals.

thanks Giora

[Anonymous]

Hi

Sales Apr file ->Have the original columns( say 10 columns) + 1 new concatenated column. Delete the dupicated columns as they are not required in the data model. So, total 11 columns

Sale May file -> This has the original columns (10) only

If you append both now, all 10 columns data will be appended and FOr the 11th column you will see data for Apr & null for May month.

Hope this clarifies you.

Thanks
Raj

[Anonymous]

This would be easier to advise on if you could upload a sample of the first and second file and what you want the result to look like. If your files are of the same format, then the steps and Power Query should treat them the same. You can handle changes in titles before sending it through the logic that adds calculated columns/appends to ensure the data is treated the same.

[gtamir]

https://drive.google.com/file/d/1PAC8ncuJE-oPaA-cRnRKXQ3oaqdAihGu/view?usp=sharing
https://drive.google.com/file/d/1lggWeBGRKyh4YdlLbWX04Y99o7mbDz-L/view?usp=sharing
https://drive.google.com/file/d/1l5fW2O80YMwIlkk0PQoWET00Zf5K20NH/view?usp=sharing

Thank you. I attached 3 files. File A, File B are the source files, and AppendTest.PBIX.

• load File A ==> Rename to Sales ==> Merge columns PharmacyCode and CustomerCode to CustomerKey.

         The old columns are gone. (is it possible to merge as new?)

• Load and Apply.
• Load file B ==> In Querry, Merge querry to Sales ==> Load and Apply.
• The result in the merged file is that in the 8 bottom records CustomerKey are empty. It didn't merge the columns for the appended file.

Thanks Giora

[v-yuta-msft · Accepted solution]

Hi gtamir,

"is it possible to merge as new?"

<-- Yes, change step Merged Columns with M code below:

= Table.AddColumn(#"Changed Type", "Merged", each Text.Combine({Text.From([PharmacyCode], "zh-CN"), Text.From([CustomerCode], "zh-CN")}, "-"), type text)

"It didn't merge the columns for the appended file."

<-- "Append" operation will not automactically merge columns in the appended table. As a workaround, to achieve your requirement, you can append 'Sheet1' to 'Sales' firstly, then Merge columns PharmacyCode and CustomerCode as a new merged column.

Hope it's helpful to you.

Jimmy Tao

[gtamir]

Thank you. I will have to do this every month.

I think the solution will be to upload the new file, do some changes, and then append.

Giora