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

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

 

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"