Forum Discussion

gtamir's avatar
gtamir
Post 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

     

Anonymous's avatar
Anonymous
Not applicable
8 years ago

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's avatar
    gtamir
    Post 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
      Community 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
        Post 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