Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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...
Need Help
Show and Tell
  • vivran22's avatar
    vivran22
    6 years ago

    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

     

vivran22's avatar
vivran22
Community Champion
6 years ago

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's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
      vivran22
      Community Champion
      6 years ago

      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