Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
My source database looks as follows after import. Can I transpose it in Power Query Editor in a way that the new column names become ID, Category, Name, Location, Title?
If Category is A, I have an additional line (Location).
Solved! Go to Solution.
Here is the code that you could copy and paste into a blank query for reference, I am sure there is a more efficent way of doing it:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8nRR0lEyNDJWitWJVnJOLElNzy+qBAo5ggX8EnNTgRxX52Aw1yc/ObEkMz8PKOSUmpmVmZcOFg7JLCnJAakLSsxNzAMLATlYaah1JujWOSFb5+UItQ9ucLB3ADHGmhLwhWOwpyOGR/KLS/LzUK1zC/EMUoqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Column2] <> null)),
#"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each ([Column2] <> "")),
#"Added Custom" = Table.AddColumn(#"Filtered Rows1", "ID", each try Number.FromText([Column2]) > 0),
#"Expanded ID" = Table.ExpandRecordColumn(#"Added Custom", "ID", {"Value"}, {"ID.Value"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded ID",{{"ID.Value", type text}}),
#"Added Custom1" = Table.AddColumn(#"Changed Type1", "Temp ID", each if [ID.Value] = "true" then [Column2] else null),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"ID.Value"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Removed Columns",{{"Temp ID", Int64.Type}}),
#"Filled Down" = Table.FillDown(#"Changed Type2",{"Temp ID"}),
#"Pivoted Column" = Table.Pivot(#"Filled Down", List.Distinct(#"Filled Down"[Column1]), "Column1", "Column2"),
#"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column",{"Temp ID"})
in
#"Removed Columns1"
Here is the code that you could copy and paste into a blank query for reference, I am sure there is a more efficent way of doing it:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8nRR0lEyNDJWitWJVnJOLElNzy+qBAo5ggX8EnNTgRxX52Aw1yc/ObEkMz8PKOSUmpmVmZcOFg7JLCnJAakLSsxNzAMLATlYaah1JujWOSFb5+UItQ9ucLB3ADHGmhLwhWOwpyOGR/KLS/LzUK1zC/EMUoqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Column2] <> null)),
#"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each ([Column2] <> "")),
#"Added Custom" = Table.AddColumn(#"Filtered Rows1", "ID", each try Number.FromText([Column2]) > 0),
#"Expanded ID" = Table.ExpandRecordColumn(#"Added Custom", "ID", {"Value"}, {"ID.Value"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded ID",{{"ID.Value", type text}}),
#"Added Custom1" = Table.AddColumn(#"Changed Type1", "Temp ID", each if [ID.Value] = "true" then [Column2] else null),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"ID.Value"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Removed Columns",{{"Temp ID", Int64.Type}}),
#"Filled Down" = Table.FillDown(#"Changed Type2",{"Temp ID"}),
#"Pivoted Column" = Table.Pivot(#"Filled Down", List.Distinct(#"Filled Down"[Column1]), "Column1", "Column2"),
#"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column",{"Temp ID"})
in
#"Removed Columns1"
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
117 | |
104 | |
77 | |
73 | |
52 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |