If value is TRUE, replace value from another table where one column equals another column

[NMorenoLAC]

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.

[NMorenoLAC]

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! 😊

[christinepayton]

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. 🙂

[NMorenoLAC]

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.

[christinepayton]

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. 🤣

[NMorenoLAC]

Thanks. I'll try this method out and see how it goes. 🙂

[NMorenoLAC]

Hello Christine... was browsing google and stumbled upon this interesting article. It's far too advanced for me, but was thinking maybe it was something you could incorporate with your youtube videos to pull the tag labels using Power Automate. 😉

https://hamsandwich.hashnode.dev/parsing-custom-planner-labels-with-power-automate 

[dufoq3 · Accepted solution]

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

[NMorenoLAC]

This 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!

[amustafa]

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/

[NMorenoLAC]

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.