transpose row to column: uneven data source

[bekirsalgin]

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).  

[Anonymous · Accepted solution]

@bekirsalgin,

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"