Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Amardeep100115
Post Prodigy
Post Prodigy

Unique values from multiple table

Hi Super Users,

 

Cold you please help me with creating a one table which must have unique column value from multiple tables.

 

The table should contains column like Date, Month&Year, Month, Year, Region, Country, City, Pin Code 

 

One more thing  i am using below DAX to achive the 

Measure = 
SWITCH (
    TRUE,
    SELECTEDVALUE ( 'Date Slicer'[Month] ) = BLANK ()
        && SELECTEDVALUE ( 'Date Slicer'[FiscalYear] ) <> BLANK (), CALCULATE (
        DISTINCTCOUNT ( Sheet1[Customer ID] ),
        FILTER (
            Sheet1,
            MONTH ( Sheet1[Month&Year] )
                = MONTH ( TODAY () ) - 1
                && Sheet1[Year]
                    = CALCULATE (
                        VALUE ( ( MAX ( 'Date Slicer'[Year] ) ) ),
                        FILTER (
                            'Date Slicer',
                            'Date Slicer'[FiscalYear] = SELECTEDVALUE ( 'Date Slicer'[FiscalYear] )
                        )
                    )
        )
    ),
    SELECTEDVALUE ( 'Date Slicer'[Month] ) = BLANK ()
        && SELECTEDVALUE ( 'Date Slicer'[FiscalYear] ) = BLANK (), CALCULATE (
        DISTINCTCOUNT ( Sheet1[Customer ID] ),
        FILTER (
            Sheet1,
            MONTH ( Sheet1[Month&Year] )
                = MONTH ( TODAY () ) - 1
                && Sheet1[Year] = YEAR ( TODAY () )
        )
    ),
    SELECTEDVALUE ( 'Date Slicer'[Month] ) <> BLANK ()
        && SELECTEDVALUE ( 'Date Slicer'[FiscalYear] ) <> BLANK (), CALCULATE (
        DISTINCTCOUNT ( Sheet1[Customer ID] ),
        FILTER (
            Sheet1,
            MONTH ( Sheet1[Month&Year] )
                = CALCULATE (
                    MONTH ( MAX ( 'Date Slicer'[Date] ) ),
                    FILTER (
                        'Date Slicer',
                        'Date Slicer'[Month] = SELECTEDVALUE ( 'Date Slicer'[Month] )
                    )
                )
                && Sheet1[Year]
                    = CALCULATE (
                        VALUE ( MAX ( 'Date Slicer'[Year] ) ),
                        FILTER (
                            'Date Slicer',
                            'Date Slicer'[FiscalYear] = SELECTEDVALUE ( 'Date Slicer'[FiscalYear] )
                        )
                    )
        )
    )
)

the results should be in power query

 

Sample Data  

 

Thank you!!

 

Amardeep Bhingardeve
1 ACCEPTED SOLUTION

@Amardeep100115 

 

You need to do one modification in Power Query for table PT (as there is a row with value as TBD in Pin) before combining:

 

Under Change Type, keep the "type" for column as text:

 

amardeep.PNG

 

 

Then add this in the Advanced Editor of a Blank Query:

let
    Source = Table.Combine({VF, PT}),
    #"Capitalized Each Word" = Table.TransformColumns(Source,{{"Month", Text.Proper, type text}, {"Country", Text.Proper, type text}, {"City", Text.Proper, type text}, {"COUNTRY", Text.Proper, type text}, {"CITY", Text.Proper, type text}}),
    #"Uppercased Text" = Table.TransformColumns(#"Capitalized Each Word",{ {"Region", Text.Upper, type text}}),
    #"Merged Columns" = Table.CombineColumns(#"Uppercased Text",{"CITY", "City"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"City"),
    #"Merged Columns1" = Table.CombineColumns(#"Merged Columns",{"Country", "COUNTRY"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Country"),
    #"Merged Columns2" = Table.CombineColumns(#"Merged Columns1",{"REGION", "Region"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Region"),
    #"Removed Duplicates" = Table.Distinct(#"Merged Columns2")
in
    #"Removed Duplicates"

 

You may find the solution file here

 

PS: You may need to update the source setting in the Power Query Editor to view the solution

 

Cheers!
Vivek

If it helps, please mark it as a solution
Kudos would be a cherry on the top 🙂

https://www.vivran.in/

Connect on LinkedIn

 

View solution in original post

3 REPLIES 3
vivran22
Community Champion
Community Champion

Hello @Amardeep100115 ,

 

Are you looking for combining all the tables & columns and keeping unique records only?

 

If yes, then you may use the Power Query append feature:

 

 

let
    Source = Table.Combine({HCcsv, VF, Occc, SB, PT}),
    #"Capitalized Each Word" = Table.TransformColumns(Source,{{"Month", Text.Proper, type text}, {"Country", Text.Proper, type text}, {"City", Text.Proper, type text}}),
    #"Uppercased Text" = Table.TransformColumns(#"Capitalized Each Word",{{"Type", Text.Upper, type text}, {"Region", Text.Upper, type text}}),
    #"Merged Columns" = Table.CombineColumns(#"Uppercased Text",{"CITY", "City"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"City.1"),
    #"Merged Columns1" = Table.CombineColumns(#"Merged Columns",{"Country", "COUNTRY"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Country.1"),
    #"Merged Columns2" = Table.CombineColumns(#"Merged Columns1",{"REGION", "Region"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Region.1"),
    #"Removed Duplicates" = Table.Distinct(#"Merged Columns2")
in
    #"Removed Duplicates"

 

use New Blank Query > Advanced Editor 

 

Cheers!
Vivek

If it helps, please mark it as a solution
Kudos would be a cherry on the top 🙂

https://www.vivran.in/

Connect on LinkedIn

Facing below error

one thing i was missed to mention in my query.

Please use PT and VF excel files as main files, and accordingly i wish to be have unique value from other files in one table.

the results should be month&year wise

also please share your working file so i can understand where i am wrong in my file

 

Thank you!

 

Errosr.jpg

 

Amardeep Bhingardeve

@Amardeep100115 

 

You need to do one modification in Power Query for table PT (as there is a row with value as TBD in Pin) before combining:

 

Under Change Type, keep the "type" for column as text:

 

amardeep.PNG

 

 

Then add this in the Advanced Editor of a Blank Query:

let
    Source = Table.Combine({VF, PT}),
    #"Capitalized Each Word" = Table.TransformColumns(Source,{{"Month", Text.Proper, type text}, {"Country", Text.Proper, type text}, {"City", Text.Proper, type text}, {"COUNTRY", Text.Proper, type text}, {"CITY", Text.Proper, type text}}),
    #"Uppercased Text" = Table.TransformColumns(#"Capitalized Each Word",{ {"Region", Text.Upper, type text}}),
    #"Merged Columns" = Table.CombineColumns(#"Uppercased Text",{"CITY", "City"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"City"),
    #"Merged Columns1" = Table.CombineColumns(#"Merged Columns",{"Country", "COUNTRY"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Country"),
    #"Merged Columns2" = Table.CombineColumns(#"Merged Columns1",{"REGION", "Region"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Region"),
    #"Removed Duplicates" = Table.Distinct(#"Merged Columns2")
in
    #"Removed Duplicates"

 

You may find the solution file here

 

PS: You may need to update the source setting in the Power Query Editor to view the solution

 

Cheers!
Vivek

If it helps, please mark it as a solution
Kudos would be a cherry on the top 🙂

https://www.vivran.in/

Connect on LinkedIn

 

Helpful resources

Announcements
July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.