Forum Discussion

Anmolgan's avatar
Anmolgan
Icon for Post Prodigy rankPost Prodigy
6 years ago
Solved

Need to create 3 relationships between 2 tables?

Hi I need to get some amounts details correctly which are mapped with another values think of it as below:   type bal   ty   bal   ty1  Bal A      40    A    90    A    300 B      20    B    60  ...
  • v-lid-msft's avatar
    v-lid-msft
    6 years ago

    Hi Anmolgan ,

     

    We can add a new step in Power Query Editor to meet your requirement:

     

     

     

     

    All the queries are here:

    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIxABIghiWMYWxgoBSrE63kBGQbgQRBDDMYwxAq6wxWCSRADHMYw9AQKBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Type = _t, bal = _t, ty = _t, ba = _t, ty1 = _t, Bal = _t]),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Type", type text}, {"bal", Int64.Type}, {"ty", type text}, {"ba", Int64.Type}, {"ty1", type text}, {"Bal", Int64.Type}}),
        #"NameOfYourLastStep" = Table.RenameColumns(#"Changed Type",{{"Bal", "Bal.1"}}),
        Append = Table.Combine({
            Table.SelectColumns(#"NameOfYourLastStep",{"Type","bal"}),
            Table.RenameColumns(
                Table.SelectColumns(#"NameOfYourLastStep",{"ty","ba"})
                ,{{"ty","Type"}
                ,{"ba","bal"}
                }),
            Table.RenameColumns(
                Table.SelectColumns(#"NameOfYourLastStep",{"ty1","Bal.1"})
                ,{{"ty1","Type"}
                ,{"Bal.1","bal"}
                })
        })
    in
        Append

     

    If you have any questions, please kindly ask here and we will try to resolve it.


    By the way, PBIX file as attached.


    Best regards,