Split column values based on another table

[Anonymous]

Hi All, 

in the attached phots , i have two tabels as tavble 1 and table 2 . Help to achieve the required output as shown in the photo

. 

[mussaenda]

Table 1

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("FcexDQBACMPAXai/gcA0iP3X+LixdbuRmfFoxr2NqjKLQUmmGOxusxmcGXPY3Qc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [sno = _t, cupid = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"sno", Int64.Type}, {"cupid", Int64.Type}})
in
    #"Changed Type"

Table 2

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQ0VNJRMgXiQqVYnWglIyMjKL8czDc2NobyU8F8ExMTKL8IzDc1BbHNgLgEzIeYB+JXIpkH4mcimQfilyrFxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [sno = _t, code = _t, major = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"sno", Int64.Type}, {"code", Int64.Type}, {"major", type text}}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Changed Type", {{"code", type text}}, "en-GB"), List.Distinct(Table.TransformColumnTypes(#"Changed Type", {{"code", type text}}, "en-GB")[code]), "code", "major"),
    #"Renamed Columns" = Table.RenameColumns(#"Pivoted Column",{{"5", "Major_5"}, {"6", "Major_6"}})
in
    #"Renamed Columns"

relationship

Output

[Anonymous]

Thanks for ur help .

Could you help me if i have multiple colomuns in the table two as major, minor, abservation, .. etc .. how to handle the povit query to get major_5,major_6,minor_5,minor_6,abservation_5,abservation_6 ... as columns

Kindly help me out 

[mussaenda · Accepted solution]

Hi,

You will just need to follow the steps in table 2.

Pivot Coulmn will be your friend here.

Check this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Tc05EoIwGEDhu/x1mmxcJpMCZFFBZ0Rl8fS8xBQUX/OaF4JorUWJxws1fhJVEGNM6Sve2HK31pbeocUld+dc6TN6LLl7n1qFDwY0uf+/qe+44nn6pn7DHY/TN/UvRkwS4wE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [sno = _t, code = _t, major = _t, minor = _t, abb = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"sno", Int64.Type}, {"code", Int64.Type}, {"major", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"sno", "code"}, "Attribute", "Value"),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Unpivoted Columns", {{"code", type text}}, "en-GB"),{"Attribute", "code"},Combiner.CombineTextByDelimiter("_", QuoteStyle.None),"Merged"),
    #"Pivoted Column1" = Table.Pivot(#"Merged Columns", List.Distinct(#"Merged Columns"[Merged]), "Merged", "Value")
in
    #"Pivoted Column1"

Then create relationship and you can produce you desired output.

[Anonymous]

Thanks for ur help .

Could you help me if i have multiple colomuns in the table two as major, minor, abservation, .. etc .. how to handle the povit query . 

Kindly help me out