Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi all,
Trying to get my head around this one and if anyone has some suggestions on where I can look into it more, it would be appreciated. I'm fairly new to PowerBI and Dax. Table 2 is comments that have the ID of the User in table 1. I am looking for a way for a search of [~accountid:xxxxxx] and replace it with the value in Column Name of table 1. Desired result displayed below.
Table 1: Users | |
User | Name |
User1 | John |
User2 | Joe |
User3 | Andrew |
User4 | Mike |
User5 | Sarah |
User16 | Deb |
User17 | Sam |
User18 | Lucy |
ID | Date | Comment | Desired Result |
1234 | 20/03/2020 | Thank you [~accountid:User1], I'll let [~accountid:User2] do the work. | Thank you John, I'll let Joe do the work. |
1234 | 22/03/2020 | No problem. [~accountid: User4] Can you help? | No problem. Mike Can you help? |
1234 | 24/03/2020 | Hey all - [~accountid:User4] Mike I can help | Hey all - Mike I can help |
1234 | 24/04/2020 | [~accountid:User1], [~accountid:User2], [~accountid:User3], [~accountid:User4] - all sorted! | John, Joe, Andrew, Mike - all sorted |
1456 | 19/03/2020 | Hey [~accountid: User1] | Hey John |
1435 | 20/02/2020 | Hey [~accountid:User18] - Need help with this? | Hey Lucy - Need help with this? |
Looked through various posts and can't seem to find what i'm looking for. Any suggestions welcome.
If you don't mind creating another column you can create a calculated column and use dax.
NewDivision = IF([organisation / portfolio] = "ZYX", "other", [Division])
If you have multiple organisations / portfolios values you want to do this for you can use OR or the logical or operator symbol
Hi @NiugeS ,
Try this code on Advanced Editor (I assumed you already have the User tables loaded):
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dVK7boMwFP2VW2aSGEP6WqIqGUrUZqjaiXqgcCUQxo6MUcTSb68NVDKQTMj3npcPThIvPni+d0g1ms9e1jUKbQfYlApz+MCm5dpjfuIFNIzMhpINCTeUUGIOn0UqKuhkC8lvmmWyFbrMn78aVAHzIf5uCaEPnANHvUBQBrkEXSBcpKrWE7WjLMScf5Q4JbihqBPqJOGs5A/Heg0TV7C2EYN9KnqXAvl5Nye8lxXOEK5R5Bi9YgepSbda3M2YxJAZESswQfbyzm6mHf1rX+tz2eByFl6ZmTSr3r6RSmN+Z+SHgk2lPryIXOHFH5K5uCFbtL03+OBpdu9FrwEbN1Z6pIbb8cHQW9Se+WjzndA8N1sJXEpdmN9cNruR8NZm3S0EY38=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", type text}, {"(blank).2", type text}, {"(blank).3", type text}}),
#"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"ID", Int64.Type}, {"Date", type date}, {"Comment", type text}, {"Desired Result", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Desired Result"}),
#"Added Index" = Table.AddIndexColumn(#"Removed Columns", "Index", 0, 1),
#"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Added Index", {{"Comment", Splitter.SplitTextByDelimiter("[~accountid", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Comment"),
#"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Comment", type text}}),
#"Added Index1" = Table.AddIndexColumn(#"Changed Type2", "Index.1", 0, 1),
#"Added Custom" = Table.AddColumn(#"Added Index1", "XPTO", each try Text.Range([Comment], 1,
Text.PositionOf([Comment], "]")-1)
otherwise null),
#"Trimmed Text" = Table.TransformColumns(#"Added Custom",{{"XPTO", Text.Trim, type text}}),
#"Cleaned Text" = Table.TransformColumns(#"Trimmed Text",{{"XPTO", Text.Clean, type text}}),
#"Merged Queries" = Table.NestedJoin(#"Cleaned Text", {"XPTO"}, User, {"User"}, "User", JoinKind.LeftOuter),
#"Expanded User" = Table.ExpandTableColumn(#"Merged Queries", "User", {"Name"}, {"Name"}),
#"Sorted Rows" = Table.Sort(#"Expanded User",{{"Index", Order.Ascending}}),
#"Removed Columns1" = Table.RemoveColumns(#"Sorted Rows",{"XPTO"}),
#"Split Column by Delimiter1" = Table.SplitColumn(#"Removed Columns1", "Comment", Splitter.SplitTextByDelimiter("]", QuoteStyle.Csv), {"Comment.1", "Comment.2"}),
#"Changed Type3" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Comment.1", type text}, {"Comment.2", type text}}),
#"Added Custom1" = Table.AddColumn(#"Changed Type3", "Text", each if [Name] = null then [Comment.1] else
[Name] & [Comment.2]),
#"Removed Columns2" = Table.RemoveColumns(#"Added Custom1",{"Comment.1", "Comment.2", "Name"}),
#"Sorted Rows1" = Table.Sort(#"Removed Columns2",{{"Index.1", Order.Ascending}}),
#"Added Custom2" = Table.AddColumn(#"Sorted Rows1", "Index_", each [Index] + ([Index.1] / 10)),
#"Changed Type4" = Table.TransformColumnTypes(#"Added Custom2",{{"Index_", type number}}),
#"Sorted Rows2" = Table.Sort(#"Changed Type4",{{"Index_", Order.Ascending}}),
#"Added Custom3" = Table.AddColumn(#"Sorted Rows2", "Custom", each Table.Group(#"Sorted Rows2", {"ID", "Index"}, {{"Valor", each Text.Combine([Text])}})),
#"Removed Columns3" = Table.RemoveColumns(#"Added Custom3",{"Index.1", "Text", "Index_"}),
#"Removed Duplicates" = Table.Distinct(#"Removed Columns3", {"ID", "Date"}),
#"Expanded Custom" = Table.ExpandTableColumn(#"Removed Duplicates", "Custom", {"Index", "Valor"}, {"Index.1", "Valor"}),
#"Added Custom4" = Table.AddColumn(#"Expanded Custom", "Filter", each if [Index] = [Index.1] then 1 else 0),
#"Filtered Rows" = Table.SelectRows(#"Added Custom4", each ([Filter] = 1)),
#"Removed Columns4" = Table.RemoveColumns(#"Filtered Rows",{"Index", "Index.1", "Filter"})
in
#"Removed Columns4"
Ricardo
@camargos88 WOW! I need to find some time to try this! Thank you so much! This is fantastic!
Hi @NiugeS ,
I hope I it helped you, if possible, please mark this as an solution and kudos.
Thanks,
Ricardo
@camargos88 Will do and thank you. I hope to have a look in the next few days..
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
96 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |