Appending two table within power query, in a way that duplicate is not created for a specific column

Hi Timahake8, there's multiple ways you could do that, either with a fancy more complicated custom M-Code or directly via the built in functions. I'll first show you how to achieve it with the built in functions (though a custom code via the advanced editor might be more efficient), because it's easier to reproduce yourself if you need it in the future:

For this solution you'll need two tables:

• The one where you actually append everything and merge your helper back into it
• A helper table for grouping

Full code for Table 1 (the one where you append everything:

let
    Source = Table.Combine({Map1, Map2, Map3}),
    #"Sorted Rows" = Table.Sort(Source,{{"Month_Year", Order.Ascending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1, Int64.Type),
    #"Merged Queries" = Table.NestedJoin(#"Added Index", {"Month_Year", "City"}, IndexLookup, {"Month_Year", "City"}, "IndexLookup", JoinKind.LeftOuter),
    #"Expanded IndexLookup" = Table.ExpandTableColumn(#"Merged Queries", "IndexLookup", {"FirstIndex"}, {"FirstIndex"}),
    #"Added Conditional Column" = Table.AddColumn(#"Expanded IndexLookup", "Test_new", each if [Index] = [FirstIndex] then [Test] else null),
    #"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column",{"Test"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Test_new", "Test"}})
in
    #"Renamed Columns"

Full code for Table 2 (the helper table):

let
    Source = Table.Combine({Map1, Map2, Map3}),
    #"Sorted Rows" = Table.Sort(Source,{{"Month_Year", Order.Ascending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1, Int64.Type),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Index",{"Month_Year", "City", "Index"}),
    #"Grouped Rows" = Table.Group(#"Removed Other Columns", {"Month_Year", "City"}, {{"FirstIndex", each List.Min([Index]), type number}})
in
    #"Grouped Rows"

Steps:

1) Select one of your three orginal Map-tables and select Home > Append Queries > Append as new
=> this table will be your final table, so you can name it however you want it in your model later; I'll call it "Appended" for the rest of this description

2) Sort the rows in your table ascending by Month_Year

3) Add an Index-column via Add Column > Index Column > From 0

4) Duplicate your "Appended" table. I've called mine "IndexLookup" but call it however you want. Disable load for this table as we only need it as a helper-table. Important note: Use DUPLICATE, not REFERENCE since we'll join it back into the Appended table later and if you use reference you'd join it back into its source.

5) Remove all Columns except Month_Year, City and the Index from Step 3)

6) Use Transform > "Group by" with "Advanced" to group the table by Month_Year and City and add a new column "FirstIndexd" with Operation "Min" on the Column "Index". We need this step to remove the additional "Test" values later:

7) Select your "Appended" table and apply Home > "Merge Queries". Select Month_Year = Month_Year and City = City for the keys of your merge.

8 ) Expand the Column "IndexLookup" (or whatever name you used for your query) and only keep the "FirstIndex" Column 

9) Add a conditional column (or a custom column, depending on your preference) via Add Column and create a new column ("Test_new" in my case since it will replace the existing "Test" column later) with the following condition:

if [Index] = [FirstIndex] then [Test] else null

10) Remove the original "Test" Column and rename your "Test_New" (or whatever you called it) to "Test". Change the data-type of the column as needed and you're done 😊

-------

Alternatively you can also do it all in a single advanced query:

let
    AllRows =
        Table.Combine({
            Map1,  // rename this as needed for your own tables!    
            Map2,     
            Map3       
        }),

    Sorted =
        Table.Sort(
            AllRows,
            {{"Month_Year", Order.Ascending},
             {"City",       Order.Ascending},
             {"Map",        Order.Ascending}} 
        ),

    Grouped =
        Table.Group(
            Sorted,
            {"Month_Year","City"},
            {
                {"Data", (t) =>
                    let
                        t_ix  = Table.AddIndexColumn(t, "ix", 0, 1, Int64.Type),
                        t_tmp = Table.AddColumn(
                                    t_ix, "Test_tmp",
                                    each if [ix] = 0 then [Test] else null,
                                    type nullable number
                                ),
                        t_out = Table.RenameColumns(
                                    Table.RemoveColumns(t_tmp, {"ix","Test"}),
                                    {{"Test_tmp","Test"}}
                                )
                    in
                        t_out,
                 type table [Month_Year=date, City=text, Test=nullable number, Map=text, Amount=text, #"Colour Rag"=text]}
            }
        ),

    Result = Table.Combine(Grouped[Data])
in
    Result

Important note: rename the tables according to your own table-names at the "AllRows" step!