Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Multiple IDs

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 ...
  • ziying35's avatar
    ziying35
    6 years ago

    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's avatar
Anonymous
Not applicable
6 years ago

 

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"