How to create a key for a list of strings

[Javier_Iglesias]

Hi,

I have an Excel text that I imported into Power BI, and I am trying to create some Dimensional tables for a central Facts one. One of the columns has a list of strings on each row, a combination of 4 possible selections in the original Excel. It will be A, B, C, D or any combination. I have followed these steps:

1. I created a Dimensional table with one of the terms per row and an index column to use as a key column.
2. In the facts table, I have substituted (query) all the terms by their corresponding number (key), obtaining a list of numbers e.g. 1,3,4.
3. In the model, I linked the Dimensional table with the key to the facts table with the string of keys.

The problem I find is that Power BI only recognizes the first element of the list to assign a key, and I cannot later use this dimensional table to filter correctly. I usually do not have this problem using R or some databases. How should be done in Power BI?

I really appreciate any help you can provide.

Javier

[v-xinruzhu-msft · Accepted solution]

Hi @Javier_Iglesias 

You can refer to the following example.

You can put the following code to the advanced editor in power query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrQ0sDD3UNJRUiACh2fm5GQm5irF6kSD+c75uQWJeZUKhkB2ZGoxkHRJTc7WUQhzVgAxQKJQDDPBKzEvVcElPxXIzAIy9VLyUx0qKir0kvNzwSrQTTYCsj3yc1KAlD9QZ0BiemoRkrEwjN9YDFON4e4NSCwqAVJ+aB4l0TwTkBH5QALqcR0FkGMLoI4lw3BQrFgQGyvB+QUZmal4IgXqSRrFBYVhh4gLtCQTSZ55+OOC1IQTCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Tech ID" = _t, Company = _t, #"Campaign status" = _t, #"Marketing Material" = _t, Out = _t, Meetings = _t, #"Agreement Negotiation" = _t, #"Term Sheet" = _t, Contact = _t, #"e-mail" = _t, #"Marketing Manager(s)" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Tech ID", type text}, {"Company", type text}, {"Campaign status", type text}, {"Marketing Material", type text}, {"Out", type text}, {"Meetings", type text}, {"Agreement Negotiation", type text}, {"Term Sheet", type text}, {"Contact", type text}, {"e-mail", type text}, {"Marketing Manager(s)", type text}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"Marketing Material"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each ([Marketing Material] <> " ")),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Filtered Rows", {{"Marketing Material", Splitter.SplitTextByEachDelimiter({","}, QuoteStyle.Csv, false), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Marketing Material"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Marketing Material", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type1"," ","",Replacer.ReplaceText,{"Marketing Material"}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Replaced Value", "Marketing Material", "Marketing Material - Copy"),
    #"Uppercased Text" = Table.TransformColumns(#"Duplicated Column",{{"Marketing Material - Copy", Text.Upper, type text}}),
    #"Removed Duplicates" = Table.Distinct(#"Uppercased Text", {"Marketing Material - Copy"}),
    #"Removed Columns" = Table.RemoveColumns(#"Removed Duplicates",{"Marketing Material - Copy"}),
    #"Added Index" = Table.AddIndexColumn(#"Removed Columns", "Index", 1, 1, Int64.Type),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Index",{"Index", "Marketing Material"})
in
    #"Reordered Columns"

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrQ0sDD3UNJRUiACh2fm5GQm5irF6kSD+c75uQWJeZUKhkB2ZGoxkHRJTc7WUQhzVgAxQKJQDDPBKzEvVcElPxXIzAIy9VLyUx0qKir0kvNzwSrQTTYCsj3yc1KAlD9QZ0BiemoRkrEwjN9YDFON4e4NSCwqAVJ+aB4l0TwTkBH5QALqcR0FkGMLoI4lw3BQrFgQGyvB+QUZmal4IgXqSRrFBYVhh4gLtCQTSZ55+OOC1IQTCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Tech ID" = _t, Company = _t, #"Campaign status" = _t, #"Marketing Material" = _t, Out = _t, Meetings = _t, #"Agreement Negotiation" = _t, #"Term Sheet" = _t, Contact = _t, #"e-mail" = _t, #"Marketing Manager(s)" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Tech ID", type text}, {"Company", type text}, {"Campaign status", type text}, {"Marketing Material", type text}, {"Out", type text}, {"Meetings", type text}, {"Agreement Negotiation", type text}, {"Term Sheet", type text}, {"Contact", type text}, {"e-mail", type text}, {"Marketing Manager(s)", type text}}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type", {{"Marketing Material", Splitter.SplitTextByEachDelimiter({","}, QuoteStyle.Csv, false), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Marketing Material"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Marketing Material", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type1"," ","",Replacer.ReplaceText,{"Marketing Material"}),
    #"Merged Queries" = Table.FuzzyNestedJoin(#"Replaced Value", {"Marketing Material"}, #"Table (2)", {"Marketing Material"}, "Table (2)", JoinKind.LeftOuter, [IgnoreCase=true, IgnoreSpace=true]),
    #"Expanded Table (2)" = Table.ExpandTableColumn(#"Merged Queries", "Table (2)", {"Index"}, {"Table (2).Index"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded Table (2)",{"Tech ID", "Company", "Campaign status", "Table (2).Index", "Marketing Material", "Out", "Meetings", "Agreement Negotiation", "Term Sheet", "Contact", "e-mail", "Marketing Manager(s)"})
in
    #"Reordered Columns"

Then apply them to the report and create 1:N relationship between two tables:

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Javier_Iglesias]

This worked perfectly; thanks a lot!!!!

[Greg_Deckler]

@Javier_Iglesias Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[Javier_Iglesias]

Thanks a lot @Greg_Deckler ,

I have something like this:

Project	Company	Materials
CG-19087	XXX	VC Deck, One Pager
CG-90821	XXY	One Pager, Non-Con Deck
CG-87621	XYY	VC Deck, Non-Con Deck
CG-78652	YYY	Non-Con-Deck

I expect to create a Dimensional table from the previous original plain table that comes from Excel, something like:

IDKey	Material
1	Non-Con Deck
2	VC Deck
3	One Pager

And a Facts table such as:

Project	Company	Materials
CG-19087	XXX	2, 3
CG-90821	XXY	3, 1
CG-87621	XYY	2,1
CG-78652	YYY	1

And then make a relationship between The IDKey in the dimensional table and the Materials in the Facts table to filter my visuals. How does this work?

I hope that is better explained now; I am not an English native speaker.

Best,

Javier

[Javier_Iglesias]

Let's see if I can rephrase the problem that I have. This is how it looks like the Excel that I have:

Tech ID	Company	Campaign status	Marketing Material	Out	Meetings	Agreement Negotiation	Term Sheet	Contact	e-mail	Marketing Manager(s)
19087H										William
	Company 1	Yes	Deck, VC Deck	Y	Y			Jane Doe	jane.doe@xxx.com
	Company 2	Hold	One Pager	Y	Y	Y	Y	Jane Doe	jane.doe@xxx.com
	Company 3	Yes	Part	N				Jane Doe	jane.doe@xxx.com
	Company 4	No	VC Deck, One pager	N				Jane Doe	jane.doe@xxx.com
19088H										Sophie
	Company 1	Yes	Part	Y	Y			Jane Doe	jane.doe@xxx.com
	Company 2	Hold	One Pager	N				Jane Doe	jane.doe@xxx.com
	Company 3	Yes	Deck	Y	Y	Y		Jane Doe	jane.doe@xxx.com
	Company 4	No	VC Deck, One pager	Y	Y	Y	Y	Jane Doe	jane.doe@xxx.com

I want to create a Star model to use a dimensional table with the Marketing Material information to filter my visualizations. How do I do it? 

Many thanks in advance community!