Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Need relationshp btw 2 tables Table-A Table-B which are not having common column for Merging 2 table

TABLE A

nameIDresultmethod   
A1232cod   
A12530mod   
B124643cod   
C13851mod   
C13923eod   
D52722cod   
D5303mod   
D52940.3eod   
C1402.82cod  

 

 

 

TABLE B

A IDB IDC IDD ID 
1221246138527 
1231241139530 
1251200140529 
1291234142520 
     

 

result:

 

3 Replies

  • PijushRoy's avatar
    PijushRoy
    Icon for Community Champion rankCommunity Champion

    Hi Anonymous 

    Unpivot the Table B, Copy and paste the code in Advance editor

    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Tcu7DcAgDEXRVZBrCv9I8CyI/dfAfklBcy356K1Foko9608dm9mhb7bR7sX2sYCj2PjiAeZ6iTPWcXGAzcEK/tf7AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"A ID" = _t, #"B ID" = _t, #"C ID" = _t, #"D ID" = _t, #" " = _t]),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"A ID", Int64.Type}, {"B ID", Int64.Type}, {"C ID", Int64.Type}, {"D ID", Int64.Type}, {" ", type text}}),
        #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{" "}),
        #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {}, "Attribute", "Value")
    in
        #"Unpivoted Columns"

     

    Create Relation


    Create visual


    PBIX File link - https://drive.google.com/file/d/17bzyqn66L_Tsc6a9ND7nR8fix7dmImuj/view?usp=sharing 


    If solve, mark as RESOLVED. Appreaited your Like/Kudis