Multiple IDs

[Anonymous]

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.

[ziying35 · Accepted solution]

@Anonymous 

Try this:

// Table1
let
    Source = Table.FromRecords(Json.Document(Binary.Decompress(Binary.FromText("i65WCslMzk4t8XRRsjLUUfIsLi5NDaksSAXzgksSS0qLlayMa3WQ1RnhUmeOqs4YRZ0xQp0JqjoTFHWWyPbGAgA=", BinaryEncoding.Base64),Compression.Deflate))),
    trans = Table.TransformColumns(Source, List.Transform(List.Skip(Table.ColumnNames(Source)), each {_, (val)=> fx(Table2, _, val)}))
in
    trans

// fx
(tbl, item, val)=> tbl[Label]{Table.PositionOf(tbl, [Property=item, Value=val], 0, {"Property", "Value"})}

// Table2
let
    Source = Table.FromRecords(Json.Document(Binary.Decompress(Binary.FromText("i65WCijKL0gtKqlUslLyLC4uTQ2pLEhV0lHySUxKzQGKBRRl5pWkFgFFwhJzSlOVrAxrdQhqckpMzi4tQOgxJkJPcGJOajFCiyWaluCSxJLSYiT1fqnluC3AUO2cn1uQk1qSitBiQkhLeGJmSWZeuoJzaXFJfi5yEJjXxgIA", BinaryEncoding.Base64),Compression.Deflate)))
in
    Source

[Anonymous]

make a pivot in this way of the table Labels:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8iwuLk0NqSxIVdJRCijKzCtJLQKyDJVidVDlnBKTs0sLgAxjDKngxJzUYiBtCZYJLkksKQVx/VLL4crhgs75uQU5qSUgbSaoMuGJmSWZeekKzqXFJfm5YFeYK8XGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Property = _t, Label = _t, Value = _t]),
    #"Pivoted Column" = Table.Pivot(Source, List.Distinct(Source[Property]), "Property", "Label")
in
    #"Pivoted Column"

 then use this in the table with IDs:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSAWNjpVidaCUjKM8czDMGi+somYB5JkCWJUhEQSk2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [TicketID = _t, IssueType = _t, Status = _t]),

    tra=Table.TransformRows(Source, each _&[IssueType=Labels{[Value=[IssueType]]}[IssueType],Status=Labels{[Value=[Status]]}[Status]] )

in
    Table.FromRecords(tra)

you get wath you expect:

PS

the error is due to an ID which is "3 " and not "3"

[Jimmy801]

Hello @Anonymous 

you can unpivot table1, Join it with table 2, delete not necessary columns and then again pivot them

let
        Table2 = 
    let
        Source = #table
        (
            {"Property","Label","Value"},
            {
                {"IssueType","Printer","1"},	{"IssueType","Backup","3"},	{"IssueType","Sales","9"},	{"Status","New","3"},	{"Status","Complete","4"},	{"Status","Waiting Customer","7"}

                        }
        )
    in
        Source,
    Table1 = 
    let
	    Source = #table
        (
            {"TicketID","IssueType","Status"},
            {
                {"1","1","3"},	{"2","1","7"},	{"3","3","4"},	{"4","9","3"}
            }
        )
    in
        Source,
    UnpivotTable1 = Table.UnpivotOtherColumns(Table1, {"TicketID"}, "Attribute", "ValueTable1"),
    Join = Table.Join(UnpivotTable1,{"Attribute", "ValueTable1"}, Table2, {"Property", "Value"}),
    #"Removed Other Columns" = Table.SelectColumns(Join,{"TicketID", "Attribute",  "Label"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Other Columns", List.Distinct(#"Removed Other Columns"[Attribute]), "Attribute", "Label")


in
    #"Pivoted Column"

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[Anonymous]

@Jimmy801 Unpivoting is not an option. The first table is quite a lot bigger and when I try to unpivot it, it gives me an error:

Message=The type of column "CompletedDate" conflicts with the type of other columns specified in the UNPIVOT list.

[Jimmy801]

Hello @Anonymous 

seems you have an inconsistency of data type in your table. Then just go for the solution of @Anonymous 

Good luck

Jimmy

[Anonymous]

@Anonymous 

create two tables from table2-->1.Status 2. Issue type 
then join(merge) these table separately on the basis of status and issue type one after another and fetch required column and delete others.

[Anonymous]

Table 1: Name the table as "des" and copy below code and paste in advannced editor


let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8iwuLk0NqSxIVdJRCijKzCtJLQKyDJVidVDlnBKTs0sLgAxjDKngxJzUYiBtCZYJLkksKQVx/VLL4crhgs75uQU5qSUgbSaoMuGJmSWZeekKzqXFJfm5YFeYK8XGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Property = _t, Label = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Property", type text}, {"Label", type text}, {"Value", Int64.Type}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Property]), "Property", "Value", List.Sum)
in
    #"Pivoted Column"

2. Table 2 Name : tickts

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}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"IssueType"}, des, {"IssueType"}, "des", JoinKind.LeftOuter),
    #"Expanded des" = Table.ExpandTableColumn(#"Merged Queries", "des", {"Label"}, {"Label"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded des",{{"Label", "Issue Type"}}),
    #"Merged Queries1" = Table.NestedJoin(#"Renamed Columns", {"Status"}, des, {"Status"}, "des", JoinKind.LeftOuter),
    #"Expanded des1" = Table.ExpandTableColumn(#"Merged Queries1", "des", {"Label"}, {"Label"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Expanded des1",{{"Label", "Status Type"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns1",{"IssueType", "Status"})
in
    #"Removed Columns"

[Anonymous]

@Anonymous 

Is this a futureproof solution? The data gets refreshed every day, will it automatically update the data for me then?

[Anonymous]

yes, I have shared the code you can use that one it will help  you

to get the desired result.

[Anonymous]

@Anonymous 

Thanks, is there a way to do this in the ribbon as well? The data in the tables is quite bigger and doing it in the ribbon would be easier I think.