Forum Discussion

NMorenoLAC's avatar
NMorenoLAC
Regular Visitor
2 years ago
Solved

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 inf...
Need Help
  • dufoq3's avatar
    dufoq3
    2 years ago

    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

     

     

christinepayton's avatar
christinepayton
Most Valuable Professional
2 years ago

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's avatar
NMorenoLAC
Regular Visitor
2 years ago

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's avatar
    christinepayton
    Most Valuable Professional
    2 years ago

    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's avatar
      NMorenoLAC
      Regular Visitor
      2 years ago

      Thanks. I'll try this method out and see how it goes. ğŸ™‚