Denormalized table for project data

[PBIDEV_10]

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	Division	Department	Start date	End date
Cash accounting	Companies that are not obliged to deliver annual accounts (Accounts Act) must be able to generate annual accounts themselves based on bank transactions.	Division IT and digital; Division Management and consultancy; Division bank housing purchase and business development	Digital customer services; Real Estate Management; ABC Bank	1/11/2021	1/2/2022

Now as you can see the 3 divisions worked on the project: Division IT and digital, Division Management and consultancy and  Division bank housing purchase and business development (seperated by semicolon) and their corresponding departments also seperated by semicolon.

I have division table already in DWH:

Division_PK	Division
1	Division IT and digital
2	Division Management and consultancy
3	Division bank housing purchase and business development

And we have table also for department which connects to division.

Department_PK	Department	Division_FK
1	Digital customer services	1
2	Real Estate Management	2
3	ABC Bank	3

The desired output I need for this data is:

Project name	Project description	Division	Department
Cash accounting	Companies that are not obliged to deliver annual accounts (Accounts Act) must be able to generate annual accounts themselves based on bank transactions.	Division IT and digital	Digital customer services
Cash accounting	Companies that are not obliged to deliver annual accounts (Accounts Act) must be able to generate annual accounts themselves based on bank transactions.	Division Management and consultancy	Real Estate Management
Cash accounting	Companies that are not obliged to deliver annual accounts (Accounts Act) must be able to generate annual accounts themselves based on bank transactions.	Division bank housing purchase and business development	ABC Bank

I dont know how to do this. Can someone help?

Regards

PP

[v-yanjiang-msft]

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.

[BA_Pete · Accepted solution]

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