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
the out put should be as below e.g request id 318264, there are two users so I need to see Program Managers = user1 and user 2 , and when I click on user1 --> it should open team link of user1 and when I click on user2 --> it should open team link of user2.
same way for request id 333089. It has 10 users so eash users should show up and I should be able to click on each user which should open team link of respective user. Hope I make sense.
Please let me know if there is a better way to send a file as an attachment. Thank you.
Hi Patv ,
Paste the following code over the default code in a new blank query and you'll be able to see the steps I took to complete this:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("3ZTPCoJAEIdfRfYs7uw/3U2i3kM8iBgGieFu4OOnDkEGHWqCqMt+h51v2Bl+bFEwJbVyhsXs4ptBTGxDOPsN56GpOp90x3rofX8ISd13/MTrtgocOOzmcr9dpP04jvM1K+OpnwKZuVu/PJohEYrUPY9ediXBVeuphFXSfnBLwspUP2zpa+vBJ0kBBmgjLuddFBTYJ1FAaIRBpIgMYREOIeAnw/OGqwmuIbgpwc0IriW4juAKWMdfg7XwV/9WeQU=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [REQUEST_ID = _t, PROG_MANAGER = _t, PROG_MANAGERS_TEAMS_URL = _t]),
removeSpaces = Table.ReplaceValue(Source," ","",Replacer.ReplaceText,{"PROG_MANAGER", "PROG_MANAGERS_TEAMS_URL"}),
addProgMgrList = Table.AddColumn(removeSpaces, "progMgrList", each Text.Split([PROG_MANAGER], ";")),
addProgMgrUrlList = Table.AddColumn(addProgMgrList, "progMgrUrlList", each Text.Split([PROG_MANAGERS_TEAMS_URL], ";")),
addZippedLists = Table.AddColumn(addProgMgrUrlList, "listZip", each List.Zip({[progMgrList], [progMgrUrlList]})),
expandZippedLists = Table.ExpandListColumn(addZippedLists, "listZip"),
extractZippedValues = Table.TransformColumns(expandZippedLists, {"listZip", each Text.Combine(List.Transform(_, Text.From), "|"), type text}),
splitExtractedValues = Table.SplitColumn(extractZippedValues, "listZip", Splitter.SplitTextByEachDelimiter({"|"}, QuoteStyle.Csv, false), {"progMgr", "progMgrUrl"}),
remOthCols = Table.SelectColumns(splitExtractedValues,{"REQUEST_ID", "progMgr", "progMgrUrl"})
in
remOthCols
SUMMARY:
1) Create lists from both [PROG_MGRS] and [PROG_MGR_URL]
2) Zip the lists together - this assumes that your users and their URLs are in the same order in their respective fields
3) Expand zipped lists to new rows
4) Extract values with any delimiter not found in either usernames or URLs
5) Split extracted values by same delimiter
Using the table in this structure, I can ouput to either single-row or multiple row format in a Matrix visual:
Pete
Thank you very very much Pete for replying and seems it's a solution I am looking for. But it seems I did not understand the steps correclty (due to my lack of knowledge/terminology in Power BI world and also I am newbie). As per your summary steps..
SUMMARY:
1) Create lists from both [PROG_MGRS] and [PROG_MGR_URL] --> How do I create it?
2) Zip the lists together - this assumes that your users and their URLs are in the same order in their respective fields -->They are in respective field but how to zip the lists together?
3) Expand zipped lists to new rows --> How ?
4) Extract values with any delimiter not found in either usernames or URLs --> How?
5) Split extracted values by same delimiter
I am really sorry that I am asking so much of your time. I really appreciate your help which is boosting my confidence. Would you mind sending me pictures of steps? Thank you and again apologize for asking too much of your time.
This is how my sample table looklike in power bi.
Thank you!
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!