Relationship approach

[MooseWinooski]

Hello all,

Its been some time since I have used PBI so im a little dusty and I can't think my way through to a solution...

I have 2 excel files that contain related data and I want to setup a relationship to them... The 2 columns I have are very very similar and mostly only differ in formatting and also not unique...
example..
Table 1

Animals

CATS DOGS and FISH

HORSES AND PIGS

CATS DOGS and FISH

LIZARDS

Table 2

Animals

Cats, Dogs & Fish

Pigs and Horses

Pigs and Horses

Lizards

Do I need to create a 3rd table with a key to reconcile the differences? or is there another way?

Regards,
M

 

[JGordon11]

let
    Source = Excel.CurrentWorkbook(){[Name="Table4"]}[Content],
    #"Duplicated Column" = Table.DuplicateColumn(Source, "Animals", "Animals - Copy"),
    #"Uppercased Text" = Table.TransformColumns(#"Duplicated Column",{{"Animals - Copy", Text.Upper, type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Uppercased Text"," AND","",Replacer.ReplaceText,{"Animals - Copy"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",",","",Replacer.ReplaceText,{"Animals - Copy"}),
    #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1"," &","",Replacer.ReplaceText,{"Animals - Copy"}),
    #"Added Custom" = Table.AddColumn(#"Replaced Value2", "tblAddSplit", each List.Sort(Text.Split([#"Animals - Copy"]," "))),
    Source1 = Excel.CurrentWorkbook(){[Name="Table5"]}[Content],
    #"Duplicated Column1" = Table.DuplicateColumn(Source1, "Animals", "Animals - Copy"),
    #"Uppercased Text1" = Table.TransformColumns(#"Duplicated Column1",{{"Animals - Copy", Text.Upper, type text}}),
    #"Replaced Value10" = Table.ReplaceValue(#"Uppercased Text1"," AND","",Replacer.ReplaceText,{"Animals - Copy"}),
    #"Replaced Value11" = Table.ReplaceValue(#"Replaced Value10",",","",Replacer.ReplaceText,{"Animals - Copy"}),
    #"Replaced Value21" = Table.ReplaceValue(#"Replaced Value11"," &","",Replacer.ReplaceText,{"Animals - Copy"}),
    #"Added Custom1" = Table.AddColumn(#"Replaced Value21", "tblAddSplit", each List.Sort(Text.Split([#"Animals - Copy"]," "))),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "In Table 4?", each if List.PositionOf(#"Added Custom"[tblAddSplit], [tblAddSplit]) >-1 then true else false),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"Animals - Copy", "tblAddSplit"})
in
    #"Removed Columns"

pq add column.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Animals	Table		Animals	Table		Animals	Table	In Table 4?
2	CATS DOGS and FISH	Table4		Cats, Dogs & Fish	Table5		Cats, Dogs & Fish	Table5	TRUE
3	HORSES AND PIGS	Table4		Pigs and Horses	Table5		Pigs and Horses	Table5	TRUE
4	CATS DOGS and FISH	Table4		Pigs and Horses	Table5		Pigs and Horses	Table5	TRUE
5	LIZARDS	Table4		Lizards	Table5		Lizards	Table5	TRUE
6				cinch bugs and Manganese	Table5		cinch bugs and Manganese	Table5	FALSE
7				Squids and skates	Table5		Squids and skates	Table5	FALSE
8
Sheet3

 

[MooseWinooski]

let
    Source = Excel.CurrentWorkbook(){[Name="Table4"]}[Content],
    #"Duplicated Column" = Table.DuplicateColumn(Source, "Animals", "Animals - Copy"),
    #"Uppercased Text" = Table.TransformColumns(#"Duplicated Column",{{"Animals - Copy", Text.Upper, type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Uppercased Text"," AND","",Replacer.ReplaceText,{"Animals - Copy"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",",","",Replacer.ReplaceText,{"Animals - Copy"}),
    #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1"," &","",Replacer.ReplaceText,{"Animals - Copy"}),
    #"Added Custom" = Table.AddColumn(#"Replaced Value2", "tblAddSplit", each List.Sort(Text.Split([#"Animals - Copy"]," "))),
    Source1 = Excel.CurrentWorkbook(){[Name="Table5"]}[Content],
    #"Duplicated Column1" = Table.DuplicateColumn(Source1, "Animals", "Animals - Copy"),
    #"Uppercased Text1" = Table.TransformColumns(#"Duplicated Column1",{{"Animals - Copy", Text.Upper, type text}}),
    #"Replaced Value10" = Table.ReplaceValue(#"Uppercased Text1"," AND","",Replacer.ReplaceText,{"Animals - Copy"}),
    #"Replaced Value11" = Table.ReplaceValue(#"Replaced Value10",",","",Replacer.ReplaceText,{"Animals - Copy"}),
    #"Replaced Value21" = Table.ReplaceValue(#"Replaced Value11"," &","",Replacer.ReplaceText,{"Animals - Copy"}),
    #"Added Custom1" = Table.AddColumn(#"Replaced Value21", "tblAddSplit", each List.Sort(Text.Split([#"Animals - Copy"]," "))),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "In Table 4?", each if List.PositionOf(#"Added Custom"[tblAddSplit], [tblAddSplit]) >-1 then true else false),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"Animals - Copy", "tblAddSplit"})
in
    #"Removed Columns"

pq add column.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Animals	Table		Animals	Table		Animals	Table	In Table 4?
2	CATS DOGS and FISH	Table4		Cats, Dogs & Fish	Table5		Cats, Dogs & Fish	Table5	TRUE
3	HORSES AND PIGS	Table4		Pigs and Horses	Table5		Pigs and Horses	Table5	TRUE
4	CATS DOGS and FISH	Table4		Pigs and Horses	Table5		Pigs and Horses	Table5	TRUE
5	LIZARDS	Table4		Lizards	Table5		Lizards	Table5	TRUE
6				cinch bugs and Manganese	Table5		cinch bugs and Manganese	Table5	FALSE
7				Squids and skates	Table5		Squids and skates	Table5	FALSE
8
Sheet3

wow thats intense....
Thanks!

 

[JGordon11]

Or this might be more useful:

let
    Source = Excel.CurrentWorkbook(){[Name="Table4"]}[Content],
    #"Duplicated Column" = Table.DuplicateColumn(Source, "Animals", "Animals - Copy"),
    #"Uppercased Text" = Table.TransformColumns(#"Duplicated Column",{{"Animals - Copy", Text.Upper, type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Uppercased Text"," AND","",Replacer.ReplaceText,{"Animals - Copy"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",",","",Replacer.ReplaceText,{"Animals - Copy"}),
    #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1"," &","",Replacer.ReplaceText,{"Animals - Copy"}),
    #"Added Custom" = Table.AddColumn(#"Replaced Value2", "tblAddSplit", each List.Sort(Text.Split([#"Animals - Copy"]," "))),
    Source1 = Excel.CurrentWorkbook(){[Name="Table5"]}[Content],
    #"Duplicated Column1" = Table.DuplicateColumn(Source1, "Animals", "Animals - Copy"),
    #"Uppercased Text1" = Table.TransformColumns(#"Duplicated Column1",{{"Animals - Copy", Text.Upper, type text}}),
    #"Replaced Value10" = Table.ReplaceValue(#"Uppercased Text1"," AND","",Replacer.ReplaceText,{"Animals - Copy"}),
    #"Replaced Value11" = Table.ReplaceValue(#"Replaced Value10",",","",Replacer.ReplaceText,{"Animals - Copy"}),
    #"Replaced Value21" = Table.ReplaceValue(#"Replaced Value11"," &","",Replacer.ReplaceText,{"Animals - Copy"}),
    #"Added Custom1" = Table.AddColumn(#"Replaced Value21", "tblAddSplit", each List.Sort(Text.Split([#"Animals - Copy"]," "))),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "In Table4 as:", each if List.PositionOf(#"Added Custom"[tblAddSplit], [tblAddSplit]) >-1 then
         Source[Animals]{List.PositionOf(#"Added Custom"[tblAddSplit], [tblAddSplit])} else "#NA"),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"Animals - Copy", "tblAddSplit"})
in
    #"Removed Columns"

pq add column.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Animals	Table		Animals	Table		Animals	Table	In Table4 as:
2	CATS DOGS and FISH	Table4		Cats, Dogs & Fish	Table5		Cats, Dogs & Fish	Table5	CATS DOGS and FISH
3	HORSES AND PIGS	Table4		Pigs and Horses	Table5		Pigs and Horses	Table5	HORSES AND PIGS
4	CATS DOGS and FISH	Table4		Pigs and Horses	Table5		Pigs and Horses	Table5	HORSES AND PIGS
5	LIZARDS	Table4		Lizards	Table5		Lizards	Table5	LIZARDS
6				cinch bugs and Manganese	Table5		cinch bugs and Manganese	Table5	#NA
7				Squids and skates	Table5		Squids and skates	Table5	#NA
8
Sheet3