How to make URL functionality work if values are comma/colon separated values.

Hi Patv ,

No worries. Here’s the steps in answer to your questions:

1) You create these two lists as new columns. Create a new custom column, call it ‘progMgrList’, and put this in as the calculation:

Text.Split([PROG_MANAGER], ";")

Do the same again for a new column called ‘progMgrUrlList’ with this calculation:

Text.Split([PROG_MANAGERS_TEAMS_URL], ";")

2) Again, add another new custom column, call it ‘listZip’, and enter this as the calculation:

List.Zip({[progMgrList], [progMgrUrlList]})

3) At the right of the column header of your new [listZip] column, there is a button which looks like two arrows turning away from each other. Click this and choose ‘Expand to New Rows’ from the dropdown options.

4) Hit the same button again, but, this time, choose the ‘Extract Values’ option from the dropdown. When prompted to choose a delimiter, select ‘Custom’ from the dropdown and type “|“ (pipe character) into the input field. You don’t have to use the pipe character, I just used this as it’s very unlikely that it’s used in your usernames, and never used in URLs.

5) Select your [listZip] column that now contains usernames concatenated with their URLs. Go to Transform tab > Split Column > By Delimiter. Again, when choosing the delimiter to split by, go to Custom then enter the pipe character into the input box.

Pete

Hi Patv ,

1) In the matrix visual, to get the single-row view, set it up like this:

Rows = Request ID

Columns = listZip.1

Values = listZip.2

To get the multi-row view, do this:

Rows = listZip.1

Columns = Request ID

Values = listZip.2

The reason you get 'First', 'Last' etc. is that you are using a field that can theoretically have multiple values available within the visual context. This is normal. If you want to get around this you can create a measure that explicitly selects a single URL value, something like this:

_teamsURL = MAX(yourTable[listZip.2])

2) To get the sorting correct, select the Power Query step BEFORE the one where you make the progMgrList column. Multi-select (Ctrl+click) [PROG_MANAGER] and [PROG_MANAGERS_TEAMS_URL]. Go to the Transform tab > Replace Values. Put a space (" ") in the Value To Find box. Leave the Replace With box empty and hit ok. Apply your query to the model and you should get alphabetical orting.

Pete