unpivot column

[PBIDEV_10]

Hello!

I am trying to organize project data.. The data contains project list with the divisions (multiple divisions separated by semicolon) and departments who has worked on it. It also have a column called Investment. Now the Investment is for the total project and not divisionwise/departmentwise. 

when unpivot column is used to then the investment is getting replicated to all divisions.

where as what I want is that the investment should be attached to the 1st division. How can I achieve it? I am also attaching the PBI desktop file for reference.

Regards

PP

[PBIDEV_10 · Accepted solution]

I solved it with altering DAX syntax for the measure.

[PBIDEV_10]

@Greg_Deckler Can you please help me? I am quite stuck with the issue.

Regards

PP

[PBIDEV_10]

I forot to attach file: 

https://drive.google.com/drive/folders/0B8pjVO_DsqYGYWMyMTMzOWEtZTA2My00YzAwLWExNWQtZTMxZmJkN2U2MGNj...

[Greg_Deckler]

@PBIDEV_10 Unpivot before you split out the Divisions?

[PBIDEV_10]

@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

[PBIDEV_10]

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