Spliting Multiple Collumn into rows related to original Collumn

[bcosta98]

Hello, I'm having a trouble spliting tow collumns into rows and still making a relation between which collumn was the original data

This is what I have:

MessageID	SuccessUsers	ErrorUsers
1	101,102	103
2	101,102,103
3	102	101,103

And this is what I want:

MessageID	User	Status
1	101	1
1	102	1
1	103	0
2	101	1
2	102	1
2	103	1
3	102	1
3	101	0
3	103	0

In which Status = 1 represents Success and Status = 0 represents Error

[jthomson]

- Split the success column by delimiter at each instance of a comma

- Split the error column by delimiter at each instance of a comma

- Select the MessageID column and unpivot other columns

- Create some sort of conditional column that gets you a 1 or 0, I used if Text.StartsWith([Attribute],"S") then 1 else 0

[nandukrishnavs · Accepted solution]

@bcosta98 

Try this in Edit Query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTI0MNQxNDACs4yVYnWilYwQojogMR0lsLAxWBghCVQcCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [MessageID = _t, SuccessUsers = _t, ErrorUsers = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"SuccessUsers", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"MessageID"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Unpivoted Columns", {{"Value", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Value"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"MessageID", Int64.Type}, {"Value", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type1", each ([Value] <> null)),
    #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{"Value", "User"}, {"Attribute", "Status"}}),
    #"Replaced Value" = Table.ReplaceValue(#"Renamed Columns","SuccessUsers","1",Replacer.ReplaceText,{"Status"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","ErrorUsers","0",Replacer.ReplaceText,{"Status"})
in
    #"Replaced Value1"

 

Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂

[bcosta98]

Thank you very much. Could you help me if I have more than 2 collumns?