CODEWITHSUNDEEP
mergepowerbirefreshupdatespowerquery
Elvino Michel
Elvino Michel

Reputation: 447

Power Query: how can I make power query update a merge file?

I have two files which I merged.

I would like to have regular updates of these files and sometimes a column could be added or column name could be changed.

But when a column name changes, it does not change in the resulting merged file.

I would like to be make my "merged query" evolve so that it "updates" with potential new inputs. If for example one of the file being merged has one more column or a new name, I would like my query to reflect that.

How can I change this in my query? Is this possible?

Below is a minimal example of what I am trying to solve:

File one:

  |---------------------|---------------------|  
  |       Name          |      Pro1           |      
  |---------------------|---------------------|
  |         A           |         56          |     
  |---------------------|---------------------|
  |         B           |         56          |     
  |---------------------|---------------------|

File 2:

  |---------------------|---------------------|  
  |       Name          |       WEURK         |      
  |---------------------|---------------------|
  |         A           |       LALALA        |     
  |---------------------|---------------------|
  |         B           |       RTEWRFVE      |     
  |---------------------|---------------------|

Resulted merger:

  |---------------------|---------------------|---------------------|    
  |       Name          |      Pro1           |       WEURK         |      
  |---------------------|---------------------|---------------------|
  |         A           |         56          |       LALALA        |        
  |---------------------|---------------------|---------------------|
  |         B           |         56          |       RTEWRFVE      |      
  |---------------------|---------------------|---------------------|

But here, I did the following for example: changing a column name, but this has no impact on my merger...How can I make my merger "update"?

  |---------------------|---------------------|  
  |       Name          |    WALALLALALALA    |      
  |---------------------|---------------------|
  |         A           |         56          |     
  |---------------------|---------------------|
  |         B           |         56          |     
  |---------------------|---------------------|

Upvotes: 0

Views: 562

Answers (1)

Kosuke Sakai
Kosuke Sakai

Reputation: 2411

You can use Table.ColumnNames to get the list of table column names that may change.

Here is a minimum possible query.

let
    // Column names in File2 except for "Name" column.
    #"File2 Columns" =
        List.RemoveItems(Table.ColumnNames(File2), {"Name"}),

    // Merge File2 into a nested table column named "__File2".
    // You may need to replace JoinKind to another one depending on your data.
    #"Merged Queries" =
        Table.NestedJoin(File1, {"Name"}, File2, {"Name"}, "__File2", JoinKind.FullOuter),

    // Expand all the columns except for "Name" in the nested "__File2" table.
    #"Expanded File2" =
        Table.ExpandTableColumn(#"Merged Queries", "__File2", #"File2 Columns")
in
    #"Expanded File2"

This query is too simplified to be practical as it is, since it is putting assumptions such as, the merge key column "Name" will not be changed, and there are no no duplicated column names in File1 and File2.

But this would be a starting point for you.

Upvotes: 1

Related Questions