Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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!!
Solved! Go to Solution.
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
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!
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
Check out the July 2025 Power BI update to learn about new features.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
User | Count |
---|---|
70 | |
68 | |
43 | |
34 | |
26 |
User | Count |
---|---|
86 | |
50 | |
45 | |
38 | |
38 |