Forum Discussion

gtamir's avatar
gtamir
Icon for Post Patron rankPost Patron
8 years ago
Solved

Append queries (Automation)

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. ...
Need Help
  • v-yuta-msft's avatar
    v-yuta-msft
    8 years ago

    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's avatar
gtamir
Icon for Post Patron rankPost Patron
8 years ago

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's avatar
v-yuta-msft
Icon for Community Support rankCommunity Support
8 years ago

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's avatar
    gtamir
    Icon for Post Patron rankPost Patron
    8 years ago

    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