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

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's avatar
Jimmy801
Icon for Community Champion rankCommunity Champion
6 years ago

Hello Anonymous 

 

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

 

Good luck

 

Jimmy