Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
.
Solved! Go to 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.
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
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"
Then create relationship and you can produce you desired output.
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
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |