unpivot column

Greg_Deckler I couldnt do it.. Can you show and tell me?

Greg_Deckler The code goes like:

Division: 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TcyxDoMwDATQX7E8s0B/oUuHbt2iDCaxgkXqIJwg8feITKx37845HHHAtxxiUhQ+PyCNECVJpYx+cDg9+y8pJf6z1u5CUWu5koaz29fTzqQrLKWZaIKt7WEh4z6b74zNIPLBuWz3H3p/AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Division_PK = _t, #"Division " = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Division_PK", Int64.Type}, {"Division ", type text}})
in
    #"Changed Type"

Department:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("HcoxDoAgDEDRqzSdXYATiDq6uBKGhjSEKJhA9fw2bi/5PwQ0OOFachG6ID1D7sodBve3JB7aDMYpoFUdrMs2hIRhp0aZKzfRYP/FqWa/gKd2Kh3G+AE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Department_PK = _t, Department = _t, Division_FK = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Department_PK", Int64.Type}, {"Department", type text}, {"Division_FK", Int64.Type}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Department", "Department"}})
in
    #"Renamed Columns"

Project data:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZVA9T8QwDP0rVieQEHftelOv3MDAgthON7ip1UYkThU7lfj3OJUOkMj04rwPv1yvzYCyADqXCqvnuXlqhhRXZE8CuqACZgJOCmkMfqYJNMFEwW+UAZkLhrta4KG/o97pI8QiCiMBjoGqbCamjEr/dLpQFAqbRY4olpHYAH+CZmRBpz6xPNtqL37zYhd4/TCTCSY/e8Vwgp+HN2ScKRLrTnAmLEGR3dcf0u69pCJWGNaS3WKpO3+sMxKxihuFtFYjy4X+PMDZVNVkjwRn5VK0TxDKm3ckJ3gnm19Ea8XfPUzeHtr20B27dsddhV2Fx3qa2+0b", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Project name" = _t, #"Project description" = _t, Division = _t, Department = _t, #"Start date" = _t, #"End date" = _t, Investment = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Project name", type text}, {"Project description", type text}, {"Division", type text}, {"Department", type text}, {"Start date", type date}, {"End date", type date},{"Investment", Int64.Type}}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type", "Department", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Department.1", "Department.2", "Department.3"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Split Column by Delimiter1", "Division", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Division.1", "Division.2", "Division.3"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Division.1", type text}, {"Division.2", type text}, {"Division.3", type text}}),
    All = Table.UnpivotOtherColumns(#"Changed Type1", {"Project name", "Project description","Start date", "End date","Investment"}, "Attribute", "Value"),
    #"Trimmed Text" = Table.TransformColumns(All,{{"Value", Text.Trim, type text}}),
    Divisions = Table.SelectRows(#"Trimmed Text", each ([Attribute] = "Division.1" or [Attribute] = "Division.2" or [Attribute] = "Division.3")),
    #"Merged Queries1" = Table.NestedJoin(Divisions, {"Value"}, Divisons, {"Division "}, "Divisons", JoinKind.LeftOuter),
    #"Expanded Divisons" = Table.ExpandTableColumn(#"Merged Queries1", "Divisons", {"Division_PK"}, {"Division_PK"}),
    Divisions2 = Table.RemoveColumns(#"Expanded Divisons",{"Attribute"}),
    Custom1 = #"Trimmed Text",
    Departments = Table.SelectRows(Custom1, each ([Attribute] = "Department.1" or [Attribute] = "Department.2" or [Attribute] = "Department.3")),
    Departments2 = Table.RemoveColumns(Departments,{"Attribute"}),
    #"Merged Queries2" = Table.NestedJoin(Departments2, {"Value"}, DepartmentsList, {"Department"}, "DepartmentsList", JoinKind.LeftOuter),
    #"Expanded DepartmentsList" = Table.ExpandTableColumn(#"Merged Queries2", "DepartmentsList", {"Division_FK"}, {"Division_FK"}),
    #"Merged Queries" = Table.NestedJoin(
#"Expanded DepartmentsList", {"Project name", "Division_FK"},
#"Divisions2", {"Project name", "Division_PK"}, "Expanded DepartmentsList", JoinKind.LeftOuter),
    #"Expanded Expanded DepartmentsList" = Table.ExpandTableColumn(#"Merged Queries", "Expanded DepartmentsList", {"Value"}, {"Value.1"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Expanded Expanded DepartmentsList",{"Division_FK"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",{{"Value", "Department"}, {"Value.1", "Division"}})
in
    #"Renamed Columns"

PP