Combine multiple columns into several columns based on delimited values

[EllieSim]

Hello,

I am trying to combine a number of split columns (using a delimiter) into several columns (based on their original headings) in the Query Editor.

This is what the original table looks like before splitting:

Item	Issue Name	Issue Distribution	Reason for Issue	Completed
Item1	COI;COI;HW1;HW2	Name1;Name2;Name 3;Name4	For Information;For Comment;For Comment;For Validation	31.08.2020;None;None;None
Item2	null	null	null	null

This is the table once split by delimiter:

Item

Issue Name.1

Issue Name.2

Issue Name.3

Issue Name.4

Issue Distribution.1

Issue Distribution.2

Issue Distribution.3

Issue Distribution.4

Reason For Issue.1

Reason For Issue.2

Reason For Issue.3

Reason For Issue.4

Completed.1

Completed.2

Completed.3

Completed.4

Item1

COI

COI

HW1

HW2

Name.1

Name.2

Name.3

Name.4

For Information

For Comment

For Comment

For Validation

31.08.2020

None

None

None

Item2

null

null

null

null

null

null

null

null

null

null

null

null

null

null

null

null

Here is the table I am hoping to achieve in The Query Editor:

Item	Issue Name	Issue Distribution	Reason for Issue	Completed
Item 1	COI	Name1	For Information	2008.2020
Item 1	COI	Name2	For Comment	None
Item1	HW1	Name3	For Comment	None
Item1	HW2	Name4	For Validation	None
Item2	null	null	null	null
Item2	etc...

Alternatively, is there a DAX expression I can use to return multiple rows in a table output as above?

Many thanks for your help.

[Alexander76877]

Hi,  some have pointed out you need to unpivot all others than "item".

That´s correct but not the full solution. After that, you need to split the attribute by "." to and again pivot by this index.  Then you get your result.

Please find the code below. 

Please mark as solution if that´s the correct answer to your question.

Alexander

------------

let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8ixJzTVU0lFy9veEkx7hhmDSCEj6Jeam6hnCGHARYxjDBMhwyy9S8MxLyy/KTSzJzM+Dijjn5+am5pVg5YUl5mSmwBQbG+oZWOgZGRgZgAzNz0tFo2J1IM4EWZ5XmpNDAYUFxcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Item = _t, #"Issue Name.1" = _t, #"Issue Name.2" = _t, #"Issue Name.3" = _t, #"Issue Name.4" = _t, #"Issue Distribution.1" = _t, #"Issue Distribution.2" = _t, #"Issue Distribution.3" = _t, #"Issue Distribution.4" = _t, #"Reason For Issue.1" = _t, #"Reason For Issue.2" = _t, #"Reason For Issue.3" = _t, #"Reason For Issue.4" = _t, Completed.1 = _t, Completed.2 = _t, Completed.3 = _t, Completed.4 = _t]), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Item"}, "Attribute", "Value"), #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByDelimiter(".", QuoteStyle.Csv), {"Header", "Index"}), #"Pivoted Column" = Table.Pivot(#"Split Column by Delimiter", List.Distinct(#"Split Column by Delimiter"[Header]), "Header", "Value"), #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Index"}) in #"Removed Columns"

[EllieSim]

Hello,

Thanks for your reply.

Unpivitong the columns removes all the null (Items with no value), when I pivot the Attribute columns this produces errors. Is there a way around this?

Many thanks

[Alexander76877]

That is correct. You need to replace null values by "" empty values before splitting the columns.

Please mark as solution if that´s the correct answer to your question.

Alexander

[lkalawski]

@EllieSim, please check my file. Null values were preserved.

[FarhanAhmed]

This is the table once split by delimiter:

Item

Issue Name.1

Issue Name.2

Issue Name.3

Issue Name.4

Issue Distribution.1

Issue Distribution.2

Issue Distribution.3

Issue Distribution.4

Reason For Issue.1

Reason For Issue.2

Reason For Issue.3

Reason For Issue.4

Completed.1

Completed.2

Completed.3

Completed.4

Item1

COI

COI

HW1

HW2

Name.1

Name.2

Name.3

Name.4

For Information

For Comment

For Comment

For Validation

31.08.2020

None

None

None

Item2

null

null

null

null

null

null

null

null

null

null

null

null

null

null

After above step . right click on "Items" and click "Unpivot Other Columns" and you will get the desired results.

[lkalawski]

Hi @EllieSim ,

You can use Table.Split function in Power Query. I prepared the file for you with the solution:

https://gofile.io/d/kT1VOZ

_______________
If I helped, please accept the solution and give kudos! 😀

[EllieSim]

Hello,

I am not able to download the file for some reason. Is it possibe to upload it here instead?

Many thanks 🙂

[lkalawski · Accepted solution]

@EllieSim

Unfortunately, I cannot add a file directly to the forum. I can only by using some file hosting.

With your dataset, apply this code in Power Query.

let
    Source = [your source],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Item", type text}, {"Issue Name", type text}, {"Issue Distribution", type text}, {"Reason for Issue", type text}, {"Completed", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Issue Name Split", each Text.Split([Issue Name],";")),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Issue Distribution Split", each Text.Split([Issue Distribution],";")),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Reason Split", each Text.Split([Reason for Issue],";")),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Completed Split", each Text.Split([Completed],";")),
    #"Added Custom4" = Table.AddColumn(#"Added Custom3", "New Table", each Table.FromColumns({[Issue Name Split],[Issue Distribution Split],[Reason Split],[Completed Split]})),
    #"Expanded New Table" = Table.ExpandTableColumn(#"Added Custom4", "New Table", {"Column1", "Column2", "Column3", "Column4"}, {"New Table.Column1", "New Table.Column2", "New Table.Column3", "New Table.Column4"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded New Table",{"Issue Name", "Issue Distribution", "Reason for Issue", "Completed", "Issue Name Split", "Issue Distribution Split", "Reason Split", "Completed Split"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"New Table.Column1", "Issue Name"}, {"New Table.Column2", "Issue Distribution"}, {"New Table.Column3", "Reason for Issue"}, {"New Table.Column4", "Completed"}})
in
    #"Renamed Columns"

Or, if you want, I can add this file to some drive. Let me know.

_______________
If I helped, please accept the solution and give kudos! 😀

[FarhanAhmed]

Is your data is consistent ? I mean number of delimated values are fixed & upto 4 ? right ?

[amitchandak]

@EllieSim , the format not very clear, see if this can help

https://www.tutorialgateway.org/how-to-split-columns-in-power-bi/