Merging multiple IDs Power Query

Question

So I have a table containing lots of data about tickets.

There are multiple IDs in this table. The meaning of these IDs are stored in another table (all in the same table).

Example data table 1

TicketID    IssueType   Status
1              1          3
2              1          7
3              3          4
4              9          3 

Example data table 2

Property    Label           Value
IssueType   Printer           1
IssueType   Backup            3
IssueType   Sales             9
Status      New               3
Status      Complete          4
Status      Waiting Customer  7
 

I want the numbers from the first table to be replaced with the Label data matching with the Property. How can I do this?

Example solution:

TicketID    IssueType   Status
1            Printer    New
2            Printer    Waiting customer
3            Backup     Complete
4            Sales      New
 

I tried to use Combine Query's but that requires me to filter in the second table, and as soon as I remove that filter the correct Label isn't shown anymore. Thanks in advance.

Answer 1

At the most basic, add two custom columns with custom formulas in Table1

Custom column #1 for Issue type label

= (i)=> List.First(Table.SelectRows(Table2, each [Property]="IssueType" and [Value]=i[IssueType]) [Label])

Custom column #2 for Status label

= (i)=> List.First(Table.SelectRows(Table2, each [Property]="Status" and [Value]=i[Status]) [Label])

sample full code

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"TicketID", Int64.Type}, {"IssueType", Int64.Type}, {"Status", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type","IssueTypeLabel",(i)=> List.First(Table.SelectRows(Table2, each [Property]="IssueType" and [Value]=i[IssueType]) [Label])),
#"Added Custom2" = Table.AddColumn(#"Added Custom","StatusLabel",(i)=> List.First(Table.SelectRows(Table2, each [Property]="Status" and [Value]=i[Status]) [Label]))
in #"Added Custom2"

you can unpivot, merge, pivot but I suspect this method is faster for larger files

Answer 2

Considering- your first table name is- table 1 And second table name is - table 2

Now, just edit your your table 1 using Advanced Editor and just put this below code after removing the existing code-

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSAWNjpVidaCUjKM8czDMGi+somYB5JkCWJURlLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [TicketID = _t, IssueType = _t, Status = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"TicketID", Int64.Type}, {"IssueType", Int64.Type}, {"Status", Int64.Type}}),
    
    //--Here from the new transformation starts
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"TicketID"}, "Attribute", "Value"),
    #"Merged Queries" = Table.NestedJoin(#"Unpivoted Other Columns", {"Attribute", "Value"}, #"table 2", {"Property", "Value"}, "table 2", JoinKind.LeftOuter),
    #"Expanded table 2" = Table.ExpandTableColumn(#"Merged Queries", "table 2", {"Label"}, {"table 2.Label"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded table 2",{"Value"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Attribute]), "Attribute", "table 2.Label", List.Max)
in
    #"Pivoted Column"

Here below is the final output-

Answer 3

So in the meanwhile I solved my own question. I left Power Query and decided to add a column where I use CALCULATE to retrieve the data from the other table. Example would be

var number = 'table1'[IssueType]
var issue = CALCULATE(MAX('table2'[label]), 'table2'[Property]="IssueType", 'table2'[Value] = number)
return issue