Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Split column values based on another table

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

Table1.JPGTable2.JPGOutput.JPG

1 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"

2019_07_16_09_15_34_Untitled_Power_Query_Editor.png

 

Then create relationship and you can produce you desired output.

 

2019_07_16_09_19_28_Untitled_Power_BI_Desktop.png

View solution in original post

4 REPLIES 4
mussaenda
Super User
Super User

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

2019_07_15_14_56_53_Untitled_Power_BI_Desktop.png

 

Output

2019_07_15_14_55_19_Settings.png

Anonymous
Not applicable

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 

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"

2019_07_16_09_15_34_Untitled_Power_Query_Editor.png

 

Then create relationship and you can produce you desired output.

 

2019_07_16_09_19_28_Untitled_Power_BI_Desktop.png

Anonymous
Not applicable

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 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.