Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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:
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
Solved! Go to Solution.
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.
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.
This worked perfectly; thanks a lot!!!!
@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.
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
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 | 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!