Split Column by delimiter, add row but empty values

Question

Is there a way to split a column by a delimeter and add a new row.&nbsp; But when the rows are added, have the values of certain columns be empty?&nbsp;We have the following table:&nbsp;Team&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Sales&nbsp; &nbsp; CountriesRed&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;104&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; USBlue&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 234&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Italy, SpainGreen&nbsp; &nbsp; &nbsp; &nbsp;100&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; BrazilPurple&nbsp; &nbsp; &nbsp; &nbsp;435&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Germany, Poland&nbsp;I would like the result to be:&nbsp;Team&nbsp; Sales&nbsp; &nbsp; CountriesRed&nbsp; &nbsp; &nbsp;104&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;USBlue&nbsp; &nbsp; &nbsp;234&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;ItalyBlue&nbsp; &nbsp; &nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; SpainGreen&nbsp; 100&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;BrazilPurple&nbsp; 435&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;GermanyPurple&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Poland&nbsp;Is this possible?&nbsp;With normal split -&gt; add row, the sales numbers are copied to the added rows and its throwing off our totals.&nbsp; Any idea to change this effect?&nbsp;Thank you,-Thomas

anonymous · Accepted Answer

thomastatum,You can copy and paste this code into your Advanced Editor for referencelet
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkpNUdJRMjQwAZKhwUqxOtFKTjmlqUCekTFIzLMkMadSRyG4IDEzDyzrXpSamgfWYgAknYoSqzJzwBIBpUUFOSCNJsamQNI9tSg3MQ+oNSA/JzEvRSk2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Team = _t, Sales = _t, Countries = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Team", type text}, {"Sales", Int64.Type}, {"Countries", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Countries", Splitter.SplitTextByDelimiter(", ", QuoteStyle.Csv), {"Countries.1", "Countries.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Countries.1", type text}, {"Countries.2", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"Team", "Sales"}, "Attribute", "Value"),
    #"Added Conditional Column" = Table.AddColumn(#"Unpivoted Columns", "New Sale", each if [Attribute] = "Countries.1" then [Sales] else null),
    #"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column",{"Sales", "Attribute"})
in
    #"Removed Columns"

Forum Discussion

Split Column by delimiter, add row but empty values

1 Reply

Related content

Count values with delimiter

split column by delimiter in dax

Get value from text after delimiter

Splitting Column values

Counting delimited values in multiple columns