Forum Discussion
If value is TRUE, replace value from another table where one column equals another column
I'm looking to create a PowerBi dashboard using Planner data. I've setup a PowerAutomate to extract the data into a .json file. I have my data model all setup but the last piece is to get the tag information. From what I found, you can extract whether a tag is in use and it provides a true/null value but you can't extract the tag names. As a solution, I've setup a Sharepoint list that has all of the tag colors setup as columns in addition to a team column as a unique column. I've then entered all of the tag names under the respective tag colors. I'm looking to replace the true values with the actual color names based on the team associated with the task. I'm trying to leverage my SharePoint list using Power Query but have limited experience with this and can use the help. Below are sample tables.
Table: TagNames
| team | blue | aqua | darkGreen | pink | lime | red |
| Team 1 | Websites | null | null | Reports | null | null |
| Team 2 | null | Security | Support | null | Dashboards | null |
| Team 3 | Applications | null | Governance | null | null | Helpdesk |
Table: PlannerTasks
| taskId | team | tagblue | tagAqua | tagDarkGreen | tagPink | tagLime | tagRed |
| TaskID1 | Team 1 | TRUE | null | null | TRUE | null | null |
| TaskID2 | Team 2 | null | TRUE | null | null | TRUE | null |
| TaskID3 | Team 3 | null | null | TRUE | null | null | TRUE |
| TaskID4 | Team 3 | null | null | null | null | null | TRUE |
| TaskID5 | Team 2 | null | null | TRUE | null | null | null |
| TaskID6 | Team 1 | null | null | null | TRUE | null | null |
Result I'm trying to achieve:
| taskId | team | tagblue | tagAqua | tagDarkGreen | tagPink | tagLime | tagRed |
| TaskID1 | Team 1 | Websites | null | null | Reports | null | null |
| TaskID2 | Team 2 | null | Security | null | null | Dashboards | null |
| TaskID3 | Team 3 | null | null | Governance | null | null | Helpdesk |
| TaskID4 | Team 3 | null | null | null | null | null | Helpdesk |
| TaskID5 | Team 2 | null | null | Support | null | null | null |
| TaskID6 | Team 1 | null | null | null | Reports | null | null |
And ultimately, I'd like to somehow add a calculated column that will list all tags in a single column as shown below. If your open to it, I could use some help with this as well:
| taskId | team | tagblue | tagAqua | tagDarkGreen | tagPink | tagLime | tagRed | allTags |
| TaskID1 | Team 1 | Websites | null | null | Reports | null | null | Websites, Reports |
| TaskID2 | Team 2 | null | Security | null | null | Dashboards | null | Security, Dashboard |
| TaskID3 | Team 3 | null | null | Governance | null | null | Helpdesk | Governance, Helpdesk |
| TaskID4 | Team 3 | null | null | null | null | null | Helpdesk | Helpdesk |
| TaskID5 | Team 2 | null | null | Support | null | null | null | Support |
| TaskID6 | Team 1 | null | null | null | Reports | null | null | Reports |
I'm a bit of a newbie and may be going about this all wrong. If you have any other suggestions to make this easier, feel free to throw it out there and thank you advance for the assistance.
Hi NMorenoLAC,
amustafa created solution in DAX so I created one in Power Query.
Important: I'm not sure if you have null values as power query null or as text "null". I've used text "null" in my query. If you have default PQ null - let me know and I'll update the query.How to use my query:
1st: create blank query, open Advanced Editor, delete whole code and paste there my query.
Then:
Query:
//If value is TRUE, replace value from another table where one column equals another column //https://community.fabric.microsoft.com/t5/Power-Query/If-value-is-TRUE-replace-value-from-another-table-where-one/m-p/3671868#M120793 let Table_TagNames = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCklNzFUwVNJRCk9NKs4sSS0GMvNKc3IQVFBqQX5RCbp4rA5UrxFCIjg1ubQos6QSxCwtAOlCyLkkFmck5ScWpRRjmGAMFHEsKMjJTE4syczPQ7LJPb8stSgvMS85FcNVHqk5BSmpxdlKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [team = _t, blue = _t, aqua = _t, darkGreen = _t, pink = _t, lime = _t, red = _t]), Table_TagNames_HeadersLower = Table.TransformColumnNames(Table_TagNames, Text.Lower), Table_PlannerTasks = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkkszvZ0MVTSUQpJTcxVADOCQl2BVF5pTg6CwiIYqwPTbgTTboRXA6ogQrsxTLsxMdZCBRHaTXBrx0qhaTfF4nh8tqM53gw56HDbh6Y9FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [taskId = _t, team = _t, tagblue = _t, tagAqua = _t, tagDarkGreen = _t, tagPink = _t, tagLime = _t, tagRed = _t]), Source = Table_PlannerTasks, Table_PlannerTasksHeadersEdited = Table.TransformColumnNames(Source, each if Text.StartsWith(_, "tag") then Text.AfterDelimiter(Text.Lower(_), "tag") else _), MergedQueryItself = Table.NestedJoin(Table_PlannerTasksHeadersEdited, {"team"}, Table_TagNames_HeadersLower, {"team"}, "Table_TagNames_HeadersLower", JoinKind.LeftOuter), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(MergedQueryItself, {"taskId", "team", "Table_TagNames_HeadersLower"}, "Attribute", "Value"), Ad_Color = Table.AddColumn(#"Unpivoted Other Columns", "Color Tag Name", each Record.FieldOrDefault(Table.ToRecords([Table_TagNames_HeadersLower]){0}, [Attribute], null), type text), ReplaceValues_ValueColumn = Table.ReplaceValue(Ad_Color, each [Value], each [Color Tag Name], (x,y,z)=> if x = "TRUE" then z else y, {"Value"} ), #"Removed Columns" = Table.RemoveColumns(ReplaceValues_ValueColumn,{"Table_TagNames_HeadersLower", "Color Tag Name"}), #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Attribute]), "Attribute", "Value"), RenamedColumnsBackToOriginal = Table.RenameColumns(#"Pivoted Column", List.Zip({Table.ColumnNames(#"Pivoted Column"), Table.ColumnNames(Source)})), Ad_allTags = Table.AddColumn(RenamedColumnsBackToOriginal, "allTags", each Text.Combine(List.Select(Record.ToList(Record.SelectFields(_, List.Select(Record.FieldNames(_), each Text.StartsWith(_, "tag")))), each _ <> "null"), ", "), type text) in Ad_allTags
10 Replies
Hi NMorenoLAC , first off, thank you for providing such details and sample data to work it. This case was very interesting to me. I solved it. You can download the files from my shared drive. Basically I created t_[color] columns as...
t_blue = IF(
'PlannerTasks'[tagblue] = TRUE,
RELATED(TagNames[blue]),
BLANK()
)And the final 'alltags' column as...
allTags =
CONCATENATE(
IF(ISBLANK('PlannerTasks'[t_blue]), "", 'PlannerTasks'[t_blue] & ", "),
CONCATENATE(
IF(ISBLANK('PlannerTasks'[t_aqua]), "", 'PlannerTasks'[t_aqua] & ", "),
CONCATENATE(
IF(ISBLANK('PlannerTasks'[t_darkGreen]), "", 'PlannerTasks'[t_darkGreen] & ", "),
CONCATENATE(
IF(ISBLANK('PlannerTasks'[t_pink]), "", 'PlannerTasks'[t_pink] & ", "),
CONCATENATE(
IF(ISBLANK('PlannerTasks'[t_lime]), "", 'PlannerTasks'[t_lime] & ", "),
IF(ISBLANK('PlannerTasks'[t_red]), "", 'PlannerTasks'[t_red])
)
)
)
)
)https://1drv.ms/f/s!Aq3n-sopiGyqgols-1JPgWEEAo6jkQ?e=5pTDTD
If I answered your question, please mark this thread as accepted and Thums Up!
Follow me on LinkedIn:
https://www.linkedin.com/in/mustafa-ali-70133451/Appreciate your response. Dax was going to be my back up, cause I am more comfortable with it but also knew it would be a lot of replication I'd have to do for every color. The sample files definitely help as well. Thank you for the thorough response.
Hi NMorenoLAC,
amustafa created solution in DAX so I created one in Power Query.
Important: I'm not sure if you have null values as power query null or as text "null". I've used text "null" in my query. If you have default PQ null - let me know and I'll update the query.How to use my query:
1st: create blank query, open Advanced Editor, delete whole code and paste there my query.
Then:
Query:
//If value is TRUE, replace value from another table where one column equals another column //https://community.fabric.microsoft.com/t5/Power-Query/If-value-is-TRUE-replace-value-from-another-table-where-one/m-p/3671868#M120793 let Table_TagNames = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCklNzFUwVNJRCk9NKs4sSS0GMvNKc3IQVFBqQX5RCbp4rA5UrxFCIjg1ubQos6QSxCwtAOlCyLkkFmck5ScWpRRjmGAMFHEsKMjJTE4syczPQ7LJPb8stSgvMS85FcNVHqk5BSmpxdlKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [team = _t, blue = _t, aqua = _t, darkGreen = _t, pink = _t, lime = _t, red = _t]), Table_TagNames_HeadersLower = Table.TransformColumnNames(Table_TagNames, Text.Lower), Table_PlannerTasks = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkkszvZ0MVTSUQpJTcxVADOCQl2BVF5pTg6CwiIYqwPTbgTTboRXA6ogQrsxTLsxMdZCBRHaTXBrx0qhaTfF4nh8tqM53gw56HDbh6Y9FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [taskId = _t, team = _t, tagblue = _t, tagAqua = _t, tagDarkGreen = _t, tagPink = _t, tagLime = _t, tagRed = _t]), Source = Table_PlannerTasks, Table_PlannerTasksHeadersEdited = Table.TransformColumnNames(Source, each if Text.StartsWith(_, "tag") then Text.AfterDelimiter(Text.Lower(_), "tag") else _), MergedQueryItself = Table.NestedJoin(Table_PlannerTasksHeadersEdited, {"team"}, Table_TagNames_HeadersLower, {"team"}, "Table_TagNames_HeadersLower", JoinKind.LeftOuter), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(MergedQueryItself, {"taskId", "team", "Table_TagNames_HeadersLower"}, "Attribute", "Value"), Ad_Color = Table.AddColumn(#"Unpivoted Other Columns", "Color Tag Name", each Record.FieldOrDefault(Table.ToRecords([Table_TagNames_HeadersLower]){0}, [Attribute], null), type text), ReplaceValues_ValueColumn = Table.ReplaceValue(Ad_Color, each [Value], each [Color Tag Name], (x,y,z)=> if x = "TRUE" then z else y, {"Value"} ), #"Removed Columns" = Table.RemoveColumns(ReplaceValues_ValueColumn,{"Table_TagNames_HeadersLower", "Color Tag Name"}), #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Attribute]), "Attribute", "Value"), RenamedColumnsBackToOriginal = Table.RenameColumns(#"Pivoted Column", List.Zip({Table.ColumnNames(#"Pivoted Column"), Table.ColumnNames(Source)})), Ad_allTags = Table.AddColumn(RenamedColumnsBackToOriginal, "allTags", each Text.Combine(List.Select(Record.ToList(Record.SelectFields(_, List.Select(Record.FieldNames(_), each Text.StartsWith(_, "tag")))), each _ <> "null"), ", "), type text) in Ad_allTagsThis was exactly what I was looking for. Reviewing what you did and trying to replicate it across my actual dataset. Wish me luck and thank you very much!
Hiyo, if you select your task ID and team ID columns, then "unpivot other columns" (assuming your only remaining columns are the tags), then filter out null, it will put your flag names into a column for you automagically. You would do this in a separate table from your Tasks table, then relate on taskId (potentially bidirectional direction depending on which way the filter goes) - that would let you use them as filters. You could concatenate the values back together into a column on your task table with a CONCATENATEX() formula if you wanted.
This way you con't have to manually assign values for all the tags. 🙂
Thank you! It was your youtube videos that got me to where I am at this point with this project. Unfortunately for me, we have different planners within different teams that I'm trying to consolidate into one single dataset. And with every planner having different tag names for the same colors, I felt your method may not be good for me long term. There's a possibility that the tags won't be static, so I was trying to use a SharePoint list as a work around for easy updating in the future when tags change. But I'll review your video once more and see if I can make sense of it all.
Sure, the tag names constantly changing will be a thing... you could still do the unpivot for easier maintenance. What I would recommend is just naming the tags after their colors in the flow, doing the unpivot, then joining in data from somewhere else on the plan ID and tag color with whatever you're labelling it as as the third column. You can join on two fields when you merge, so that will let you differentiate the different labels for the different plans. Sharepoint list would work fine. Slightly messy but I don't see a better path there.
If you want to go full-on janky you could add a canvas app somewhere in there and bring the UI together into the PBI report. 🤣
Just wanted to say thank you to all of you. I ended up using parts of everyone's feedback to ultimately come up with a solution. I modified dufoq3 query to replace all of the color tags with the appropriate values, christinepayton to create my tag table for filtering using the "unpivot other columns method", and used a modified version of amustafa dax for the allTags column. Couldn't have done it without all of your help! 😊
