Forum Discussion
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
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 Pete,
Thank you very very much again for great step by step explanations. All steps worked. I am very close now. somehow I couldn't do two things. please see attached screenshot.
1. How did you set up all URLs in Metrix Visualization? I was able to do that in Table visulization by changing "data Category" to 'Web URL' ( attached screen). If I try to apply conditional formatting, it shows first, last count(distincts), count.
2. I am not able to order user1. user2, user3 in ascending order somehow. if you see in screen shot, it it in user2, user3, user1 order. I tried to change ascending/decending order but did not work.
Thank you!
This solves my problem. Thank you Pete for all taking time and teaching me step by steps. I really appreciate it. You should start making Youtube videos on power bi. 🙂 I learn something new in Power bi as well. Again thank you a million.
-Vpat
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