Forum Discussion
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
Thank you!!
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
3 Replies
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 LinkedInFacing 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!
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