cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Javier_Iglesias
Regular Visitor

How to create a key for a list of strings

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

1 ACCEPTED SOLUTION
v-xinruzhu-msft
Community Support
Community Support

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:

vxinruzhumsft_0-1674541326761.png

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.

View solution in original post

5 REPLIES 5
v-xinruzhu-msft
Community Support
Community Support

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:

vxinruzhumsft_0-1674541326761.png

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.

This worked perfectly; thanks a lot!!!!

Greg_Deckler
Super User
Super User

@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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Thanks a lot @Greg_Deckler ,

 

I have something like this:

 

ProjectCompanyMaterials
CG-19087XXXVC Deck, One Pager
CG-90821XXYOne Pager, Non-Con Deck
CG-87621XYYVC Deck, Non-Con Deck
CG-78652YYYNon-Con-Deck

 

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

 

IDKeyMaterial
1Non-Con Deck
2VC Deck
3One Pager

 

And a Facts table such as:

 

 

ProjectCompanyMaterials
CG-19087XXX2, 3
CG-90821XXY3, 1
CG-87621XYY2,1
CG-78652YYY1

 

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

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

 

Tech IDCompanyCampaign statusMarketing MaterialOutMeetingsAgreement NegotiationTerm SheetContacte-mailMarketing Manager(s)
19087H         William
 Company 1YesDeck, VC DeckYY  Jane Doejane.doe@xxx.com 
 Company 2HoldOne PagerYYYYJane Doejane.doe@xxx.com
 Company 3YesPartN   Jane Doejane.doe@xxx.com
 Company 4NoVC Deck, One pagerN   Jane Doejane.doe@xxx.com
19088H         Sophie
 Company 1YesPartYY  Jane Doejane.doe@xxx.com 
 Company 2HoldOne PagerN   Jane Doejane.doe@xxx.com
 Company 3YesDeckYYY Jane Doejane.doe@xxx.com
 Company 4NoVC Deck, One pagerYYYYJane Doejane.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!

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.