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

[Timahake8]

I want to append the first three table to create the last table. I do not want the test value for each city in a specific month to be repeated in my new table (I am trying to achieve table 4).

[raisurrahman]

Is it your desired format?

I assume the blank column is unintentional.

let
    Source = Table.Combine({Table1, Table2, Table3}),
    #"Added Conditional Column" = Table.AddColumn(Source, "Test1", each if [Map] = "Map 1" then [Test] else null),
    #"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column",{"Test"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Month_Year", "City", "Test1", "Map", "Amount", "Colour Rag"}),
    #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"Test1", "Test"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Month_Year", type date}, {"City", type text}, {"Test", Int64.Type}, {"Map", type text}, {"Amount", Percentage.Type}, {"Colour Rag", type text}})
in
    #"Changed Type"

I’ve loaded all three tables as connection-only queries. In Table2 and Table3, I renamed the last column header from Red to Colour Rag. If needed, header renaming can be made user-defined for greater flexibility.

[v-aatheeque]

Hi @Timahake8 

Just checking in to see if the previous response helped resolve your issue shared by @raisurrahman . If not, feel free to share your questions and we’ll be glad to assist.

[v-aatheeque]

Hi @Timahake8 

We wanted to follow up to check if you’ve had an opportunity to review the previous responses. If you require further assistance, please don’t hesitate to let us know.

[Timahake8]

Hi @v-aatheeque, I have been able to sort the problem in a different way. I needed the test column on my table so a could use it to filter my visual but since I could figure a way to make it work to get the ultimate result I wanted, I just applied the filtering within my model during transformation and solved it. Thank alot

[v-aatheeque]

Hi @Timahake8 

Glad to hear you found a solution and thanks for sharing it! Please consider posting your approach in the community it could help others facing similar issues.
Feel free to continue using the Fabric Community for any future queries.

[raisurrahman]

@Timahake8Is the blank column from output table intentional or accidental?

[p45cal]

How about combining the tables, eg.:

= Table.Combine({Table7, Table8, Table9})

then outputting (loading to) to a pivot table (I've done this in Excel, the Power BI equivalent is, I think, a matrix visual):

Above, the Month_Year and City columns are repeating their item labels:

but the Test column isn't.

Of course, you can filter on any of the columns in this pivot table.

You might prefer for none of the item labels repeating, it might be even clearer:

[KarinSzilagyi]

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!

[Timahake8]

Many thanks Karin, but if I go about it this way and want to visualise the Map column in my visual and put in test column to filter to > 30 test for instance. Map I and Map two test data is then filtered out. My end goal is to use the test column as a filter in my visual which has Map Name (Map 1, 2 and 3),  amount and colour, as you know that you cannot use a calculated measure to filter some visuals. But at the moment my test column has repeated values

[KarinSzilagyi]

Hi @Timahake8 I'm not quite sure if I understand what you mean there. I've based the implementation on your example table as a target:

Map2 + Map3 would be filtered out based on how you set up your target-table. Did you actually want a table like this?

Month_Year	City	Test	Map	Amount	Colour Rag
30-Apr-25	Birmingham	30	Map 1	0,26	Red
30-Apr-26	Birmingham	30	Map 2	0,15	Yellow
30-Apr-27	Birmingham	30	Map 3	0,1	Yellow
30-May-25	Birmingham	40	Map 1	0,22	Red
30-May-25	Birmingham	40	Map 2	0,12	Yellow
30-May-25	Birmingham	40	Map 3	0,27	Red
30-Jun-25	Coventry	31	Map 1	0,16	Red
30-Jun-26	Coventry	31	Map 2	0,16	Red
30-Jun-27	Coventry	31	Map 3	0,11	Yellow

With this you can still filter for Test > 30 and won't lose Map2 + Map3. To get this you just need to perform a regular append on your data.

[Timahake8]

Hi @KarinSzilagyi if I use the current test column on a visual to apply where Test > 30, Power bi will have added all the instances of 30 for birmingham as such I wont be filtering right. Many thanks for your input😊. Kindly let me have another think about explaining what I am trying to achieve in a more clearer way.

[BA_Pete]

Hi @Timahake8 ,

This looks like an XY Problem to me.

Can you give a bit more detail around what you're actually trying to achieve with the output please? E.g. How are you aiming to report on this Test value? Is it a numerical measure value, or a text/string attribute describing the type of test completed? Etc.
It's perfectly normal for attribute values like this to be duplicated in a fact table, with display requirements being implemented in the front-end/report rather than doctoring the data in Power Query.

Pete

[vivien57]

Hello @Timahake8 ,

Try this :

let
    Source1 = Table1,
    Source2 = Table2,
    Source3 = Table3,
    Combined = Table.Combine({Source1, Source2, Source3}),
    RemoveDuplicates = Table.Distinct(Combined, {"Month_Year", "City", "Test", "Map"})
in
    RemoveDuplicates

then add a column to the result table to handle not repeating the test value

Have a nice day,

Vivien

[Timahake8]

Many thanks for your assisting, this solution doesnt work for what I am trying to achieve.