Creating Tables of Text

[leanns]

Hello,

I'm fully sure this is possible but I'm taking data from a multiple lines column in a SharePoint List and splitting the values up by a delimiter. There are 4 of these columns and each value between each delimiter correspond. 

example: 

Description: text1;text2;text3

Contact: Name1;Name2;Name3

When: Date1;Date2;Date3

Best Action: value1;value2;value3

So text1, Name1, Date1, and value1 all go together and I want to show that as each individual line in a table within Power BI. 

I was able to create a new query for each column but now I need to combine them and I can't figure it out. I've tried merging and appending but it doesn't line the text up right. I've also tried to use the  Union(selectcoloumns()) commands in a table, but that also didn't work like I want. I am new to DAX Language so any help would be great. 

What I want is this: 

ID | Description | Contact | When | Best Action

Since these are all coming from the same SharePoint List item, I want to keep the ID that goes with them so I can add a slicer and filter by ID or by Contact etc. 

Thanks for any help given! 

[ElenaN]

Hello,

For part of the solution you can use easily Power Query. You can load your table, open Power Query (Home -> Edit Queries). In Power Query you have to perform two Split Columns actions and a Transpose to get to your desired result without the ID column.

So, on the Transform Tab you go to Split Column by Delimiter button and you split by ":" and that will get you a column with the future header values. And then go again to Transform Tab -> Split Column by Delimiter and split by ";" and you will get your future values split in three different columns. Then, again on the Transform Tab -> Transpose Rows and you get your structure.

For the ID part i do not understand how do you get the information of an ID to link to your example. Maybe you can give more information related to this.

Regards,

ElenaN

[leanns]

@ElenaN Thank you for the help but the names Description, Contact, etc are already the header names. These are the different coloumns. My bad if I didn't specify that clearly. 

The ID value is from SharePoint. It basically the record number that is unique to each new record within the list. It's a value that is generated with each new item within the list. 

The data that is pulled from this list has a bunch of columns and information. 

To be fair, I'm not even sure if what I want is possible at the time being. 

[leanns · Accepted solution]

So I figured out how to make the table. I just needed to add an index column to merge the queries together and then expand the column from there. 

Thanks for the help though!