Unique values from multiple table

[Anonymous]

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!!

[vivran22]

Hello @Anonymous ,

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

[Anonymous]

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!

[vivran22 · Accepted solution]

@Anonymous 

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:

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