Forum Discussion

PBIDEV_10's avatar
PBIDEV_10
Helper II
4 years ago
Solved

Denormalized table for project data

Hello Gurus I have an excel file as an input with project data. The data shows how many departments have worked on the project. The raw data looks like this:  Project name Project description ...
Need Help
Show and Tell
  • BA_Pete's avatar
    BA_Pete
    4 years ago

    Hi PBIDEV_10 ,

     

    Select your [Department] column and go to Transform tab > Split Column (Dropdown) > By Delimiter.

    In the first dropdown in the dialog, choose Custom and enter '; ' (semicolon space) without the apostrophes.

    Leave it to split at each occurrence of the delimiter.

    Under Advanced Options, choose to split into rows.

    I would, at this point add a conditional column or merge that gets the [Department_PK] from your dimension table, then use this in a Snowflake model to be able to control by Division as well.

     

    Pete

v-yanjiang-msft's avatar
v-yanjiang-msft
Community Support
3 years ago

Hi PBIDEV_10 ,

Have you solved the problem by BA_Pete 's steps? If so, please consider accept his reply as the solution.

I support the detailed formula below for your reference:

Division table:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TYyxCgIxEER/Zdn6GvUXbEQEC7sQZC9Zcotxc7jJgX8vl0JSzps34xyeZROTos/7Fad/Qj85PAwALg8gjRAlSaXc++PY30gp8Zu1di8UtZYrafh29zS6M+kLltJMNMHaPmEh4z6bd8ZmEHnjXNb9D73/AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Division_PK", Int64.Type}, {"Division", type text}})
in
    #"Changed Type1"

Department table:

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}})
in
    #"Changed Type"

Fact table:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZZC7bsMwDEV/hfDUAkFTe83kPIYOBYq2W5CBlglbrUwZIm2gf1/KeTRAtyuKVwdHx2PxluIXOQXGgYrV7diSuORH9ZFtuvezl0ukEZMOxGqHD7UMLWquHrg9x9PqWOxQekDn4sTqubPrXRxGZE8C2qMCJgKOCrEJvqMWNBoz+JkSIPOE4doWeKivqXb6CMMkCg0BNoFyrSOmZNx/Pe1pEAqzIRsUY0S2wN+gCVnQZTl5utODl097xCx85xXDBm4Xr8jYUbZeFpwVp6DI7uduaXm7j5OYMIxTcr1Rl/0mz0jEFGcKcbx83/7MAWdGcTBzoTR7R7KBd7L5QTR7/cE3UG93sDWOtct1Wa6r56pccpVjVZxOvw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t, #"(blank).4" = _t, #"(blank).5" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", type text}, {"(blank).2", type text}, {"(blank).3", type text}, {"(blank).4", type text}, {"(blank).5", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Project name", type text}, {"Project description", type text}, {"Division", type text}, {"Department", type text}, {"Start date", type date}, {"End date", type date}}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type1", {{"Division", Splitter.SplitTextByDelimiter("; ", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Division"),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Division", type text}}),
    #"Merge1"=Table.NestedJoin(#"Division", {"Division_PK"}, Department, {"Division_FK"}, "Department", JoinKind.LeftOuter),
    #"Merge2"=Table.NestedJoin(#"Changed Type2",{"Division"}, #"Merge1",{"Division"},"New",JoinKind.LeftOuter),
    #"Expanded New" = Table.ExpandTableColumn(Merge2, "New", {"Department"}, {"New.Department"}),
    #"Expanded New.Department" = Table.ExpandTableColumn(#"Expanded New", "New.Department", {"Department"}, {"New.Department.Department"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded New.Department",{"Department"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Project name", "Project description", "Division", "New.Department.Department", "Start date", "End date"}),
    #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"New.Department.Department", "Department"}})
in
    #"Renamed Columns"

I attach my sample below for your reference.

 

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.