Splitting columns result in duplicating values in other columns

[Joshua-Massey]

Hi all,

I have a problem whereby when i split a specific column by delimiter into rows, it duplicates values in other columns which messes with my reporting data. How can i make it so that duplicates in other columns return null instead repeating values?

Thanks

[v-jianboli-msft · Accepted solution]

Hi @Joshua-Massey 

Please try:

Split to three columns – unpivot the additional two columns.

Output:

Then duplicate the table, Remove other columns, keep the value column, add two null columns.

Output:

Change the original table – append the two table.

Final output:

Here is the M code:

Table:

let

    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUfLKL84AUok6SUqxOtFKRkC2b2JRNpCCCBgDWc5FiZnpQDpZJ0UnFSxqAtKZmJcK1pkMFjIFskMyc0EadVKUYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Column1 = _t, Column2 = _t]),

    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Column1", type text}, {"Column2", type text}}),

    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Column2", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Column2.1", "Column2.2", "Column2.3"}),

    #"Unpivoted Only Selected Columns" = Table.Unpivot(#"Split Column by Delimiter", {"Column2.2", "Column2.3"}, "Attribute", "Value"),

    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Only Selected Columns",{"Attribute", "Value"}),

    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Column2.1", "Value"}}),

    #"Appended Query" = Table.Combine({#"Renamed Columns", #"Table (2)"})

in

#"Appended Query"



Table(2):

let

    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUfLKL84AUok6SUqxOtFKRkC2b2JRNpCCCBgDWc5FiZnpQDpZJ0UnFSxqAtKZmJcK1pkMFjIFskMyc0EadVKUYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Column1 = _t, Column2 = _t]),

    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Column1", type text}, {"Column2", type text}}),

    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Column2", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Column2.1", "Column2.2", "Column2.3"}),

    #"Unpivoted Only Selected Columns" = Table.Unpivot(#"Split Column by Delimiter", {"Column2.2", "Column2.3"}, "Attribute", "Value"),

    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Only Selected Columns",{"Column2.1", "Attribute"}),

    #"Removed Other Columns" = Table.SelectColumns(#"Removed Columns",{"Value"}),

    #"Added Custom" = Table.AddColumn(#"Removed Other Columns", "Custom", each null),

    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each null),

    #"Reordered Columns" = Table.ReorderColumns(#"Added Custom1",{"Custom", "Custom.1", "Value"}),

    #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"Custom", "ID"}, {"Custom.1", "Column1"}})

in

    #"Renamed Columns"

Best Regards,

Jianbo Li

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

[AntonioM]

How does it mess with your reporting? You will probably be better off fixing that instead of trying to add the nulls in. In the table you want, you have two rows with Column 2 = d, but no data in the other columns, which is going to create more work to tell who those belong to.

Another option would be to duplicate this table and just keep ID and Column 2, then create a one-to-many link between your unique orignial table and new table on ID.