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

Jimmy801's avatar
Jimmy801
Icon for Community Champion rankCommunity Champion
6 years ago

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'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