Splitting column into multiple columns based on text value in second column

[Rasmus_A]

Hi, 

I am trying to build a Power BI dashboard based on SQL server data. My issue is that I have a table in which I need to split a single column (Component Type) into three separate columns (Bottle type, Cap type, Packaging type) based on text string values in another column (Item No) in the same table. This is to allow me to see e.g. which bottle and cap types are related (through the Item No) to a given packaging type and vice versa when I click on them in a table in report view. I have tried to illustrate what I seek to achieve in the figure snippet below. 

Basically what I want to achieve is to separate the component types into separate columns based on the two first letters in their Item No. This way I can use the production BOM No (not pictured - is in separate table) to see which Bottle type, cap type and packaging types are related depending on which drill down filter I apply. 

However, when trying to make calculated columns to achieve the above I get a circular reference error. I have tried multiple solutions such as trying to make calculated tables and even making duplicate SQL queries and filtering the tables in query editor, yet it does not seem to fix the issue. 

Any suggestions on how to achieve the above without getting a circular reference error? 

Thanks in advance, 

/Rasmus

[Anonymous · Accepted solution]

Hello 

You can use Conditional column to achive this.

Please try with this code.

let
    Source = Excel.Workbook(File.Contents("C:\CommunityHelp.xlsx"), null, true),
    Item_Sheet = Source{[Item="Item",Kind="Sheet"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Item_Sheet,{{"Column1", type text}, {"Column2", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Item No", type text}, {"Component Type", type text}}),
    #"Added Conditional Column" = Table.AddColumn(#"Changed Type1", "Bottle Type", each if Text.StartsWith([Item No], "EF") then [Component Type] else null),
    #"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "Cap Type", each if Text.StartsWith([Item No], "EK") then [Component Type] else null),
    #"Added Conditional Column2" = Table.AddColumn(#"Added Conditional Column1", "Packaging Type", each if Text.StartsWith([Item No], "EY") then [Component Type] else null)
in
    #"Added Conditional Column2"

Note: I have used excel file as input. So alter the first few lines to match you SQL database.

[Rasmus_A]

Thank you for the quick reply and delightfully simple solution!