Create a unique values list form 2 text strings in the same table

[Anonymous]

I have a list of Unique member IDs along with a list of names of the members as semicolon-delimited text strings, similar to the table below.

Each member ID is assigned to a unique Member no matter the order the ID shows.

I would like to be able to create a unique MemberID - Member table from the data source, how to do that?

MembersID	Members
1;2;3;4;5;6;7;8	AB;CD;EF;GH;IJ;KL;MN;OP
4;8;9	GH;OP;QR
3;6;1;2	KL;EF;AB;CD

The result I am expecting:

MemberID	Member
1	AB
2	CD
3	EF
4	GH
5	IJ
6	KL
7	MN
8	OP
9	QR

Thanks!

[v-diye-msft · Accepted solution]

Hi @Anonymous 

It seems you've wrote the wrong string for EF and KL.  should be:

3;6;1;2

EF;KL;AB;CD

Based on this, kindly check the solution in power query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("HYw5DoAwEMS+grZOA+EIcsV9k0Ab5f/fYEXrsSdGySmwlFTUNDgx0vUMI9PMsrLtHCfXjQ+ZJBOlxNGqpJsPPO8Prbb6o1hlDf8HSekD", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [MembersID = _t, Members = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"MembersID", type text}, {"Members", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "MembersID", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"MembersID.1", "MembersID.2", "MembersID.3", "MembersID.4", "MembersID.5", "MembersID.6", "MembersID.7", "MembersID.8"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"MembersID.1", Int64.Type}, {"MembersID.2", Int64.Type}, {"MembersID.3", Int64.Type}, {"MembersID.4", Int64.Type}, {"MembersID.5", Int64.Type}, {"MembersID.6", Int64.Type}, {"MembersID.7", Int64.Type}, {"MembersID.8", Int64.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"Members"}, "Attribute", "Value"),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Unpivoted Columns", "Members", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Members.1", "Members.2", "Members.3", "Members.4", "Members.5", "Members.6", "Members.7", "Members.8"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Members.1", type text}, {"Members.2", type text}, {"Members.3", type text}, {"Members.4", type text}, {"Members.5", type text}, {"Members.6", type text}, {"Members.7", type text}, {"Members.8", type text}}),
    #"Unpivoted Columns1" = Table.UnpivotOtherColumns(#"Changed Type2", {"Attribute", "Value"}, "Attribute.1", "Value.1"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Columns1", each true),
    #"Split Column by Delimiter2" = Table.SplitColumn(#"Filtered Rows", "Attribute", Splitter.SplitTextByDelimiter(".", QuoteStyle.Csv), {"Attribute.1.1", "Attribute.2"}),
    #"Changed Type3" = Table.TransformColumnTypes(#"Split Column by Delimiter2",{{"Attribute.1.1", type text}, {"Attribute.2", Int64.Type}}),
    #"Split Column by Delimiter3" = Table.SplitColumn(#"Changed Type3", "Attribute.1", Splitter.SplitTextByDelimiter(".", QuoteStyle.Csv), {"Attribute.1.1.1", "Attribute.1.2"}),
    #"Changed Type4" = Table.TransformColumnTypes(#"Split Column by Delimiter3",{{"Attribute.1.1.1", type text}, {"Attribute.1.2", Int64.Type}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type4",{"Attribute.1.1", "Attribute.1.1.1"}),
    #"Added Custom" = Table.AddColumn(#"Removed Columns", "Custom", each if [Attribute.2] = [Attribute.1.2]
then [Value]
else null),
    #"Filtered Rows1" = Table.SelectRows(#"Added Custom", each ([Custom] <> null)),
    #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows1",{"Attribute.2", "Attribute.1.2", "Value"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Columns1")
in
    #"Removed Duplicates"

Pbix attached.

[Anonymous]

@v-diye-msft 

that worked just great, thank you.

And yes - the string you mentioned was incorrect indeed...

Thanks!

[amitchandak]

@Anonymous , In Data Transformation/edit query you have an option for split data into rows and column

https://www.tutorialgateway.org/how-to-split-columns-in-power-bi/

[Anonymous]

@amitchandak Thank you, I have tried to split the column into new rows by delimiter, at first split the MembersID with the following result:

MemberID	Member
1	AB;CD;EF;GH;IJ;KL;MN;OP
2	AB;CD;EF;GH;IJ;KL;MN;OP
3	AB;CD;EF;GH;IJ;KL;MN;OP
4	AB;CD;EF;GH;IJ;KL;MN;OP
5	AB;CD;EF;GH;IJ;KL;MN;OP
6	AB;CD;EF;GH;IJ;KL;MN;OP
7	AB;CD;EF;GH;IJ;KL;MN;OP
8	AB;CD;EF;GH;IJ;KL;MN;OP

etc.

And then when I have split the Members column I got the following:

MemberID	Member
1	AB
1	CD
1	EF
1	GH
1	IJ
1	KL
1	MN
1	OP
2	AB
2	CD
2	EF
2	GH
2	IJ
2	KL
2	MN
2	OP

Etc.

I am unable to find a unique combination of MemeberID - Member