Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
PBIDEV_10
Helper II
Helper II

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 nameProject descriptionDivisionDepartmentStart dateEnd date
Cash accountingCompanies 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 developmentDigital customer services; Real Estate Management; ABC Bank1/11/20211/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_PKDivision 
1Division IT and digital
2Division Management and consultancy
3Division bank housing purchase and business development

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

Department_PKDepartmentDivision_FK
1Digital customer services1
2Real Estate Management2
3ABC Bank3

 

The desired output I need for this data is:

Project nameProject descriptionDivisionDepartment
Cash accountingCompanies 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 digitalDigital customer services
Cash accountingCompanies 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 consultancyReal Estate Management
Cash accountingCompanies 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 developmentABC Bank

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

 

Regards

PP

1 ACCEPTED SOLUTION
7 REPLIES 7
NickolajJessen
Solution Sage
Solution Sage

Well, what is the correct department?
Right now it's just matching up the 1.st entry of Division with 1.st entry of department. 2nd entry of division with 2nd entry of department, and so on.

@NickolajJessen I have division-department tables in the datawarehouse like mentioned in the question. 

 

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

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

Department_PKDepartmentDivision_FK
1Digital customer services1
2Real Estate Management2
3ABC Bank3

So is it possible to map the departments I get in the project with the table in DWH and create the list? Then with that I can get the corresponding division via Division_FK in the department table.

 

PP

Thank you so much! Will this work with my original department table with 590 rows?

Only one way to find out 😉

NickolajJessen
Solution Sage
Solution Sage

Paste this into advanced query editor. See if it gives you a clue.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZY8xT8QwDIX/itUJJMTRrp16hYGBBbGdbnBTq41InCp2KvHvcSMdILG9OO/587tcmhFlBXQuFVbPS/PQjCluyJ4EdEUFzAScFNIU/EIzaIKZgt8pAzIXDLe0wN1wU4PTe4hFFCYCnAIdsYWYMir9y+lKUSjshpxQjJHYBH+CZmRBpz6xPNppz373Yg94/bAlM8x+8Yqhh5+PN2RcKBJrNTgLlqDI7uuPqe5eUxErDFvJbjVq9U/HjESs4k4hbceiyq0ccNYoRWsulHfvSHp4J5u/iB69fuE9DOcRzsaxdHtq21P31LVVd4fsmuv1Gw==", 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]),
    #"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}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Start date", "End date"}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Removed Columns", "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"}, "Attribute", "Value"),
    Divisions = Table.SelectRows(All, each ([Attribute] = "Division.1" or [Attribute] = "Division.2" or [Attribute] = "Division.3")),
    #"Added Index" = Table.AddIndexColumn(Divisions, "Index", 0, 1, Int64.Type),
    Divisions2 = Table.RemoveColumns(#"Added Index",{"Attribute"}),
    Custom1 = All,
    Departments = Table.SelectRows(Custom1, each ([Attribute] = "Department.1" or [Attribute] = "Department.2" or [Attribute] = "Department.3")),
    #"Added Index1" = Table.AddIndexColumn(Departments, "Index", 0, 1, Int64.Type),
    Departments2 = Table.RemoveColumns(#"Added Index1",{"Attribute"}),
    #"Merged Queries" = Table.NestedJoin(Departments2, {"Project description", "Project name", "Index"}, Divisions2, {"Project description", "Project name", "Index"}, "Removed Columns1", JoinKind.LeftOuter),
    #"Expanded Removed Columns1" = Table.ExpandTableColumn(#"Merged Queries", "Removed Columns1", {"Value"}, {"Value.1"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded Removed Columns1",{"Project name", "Project description", "Value", "Value.1", "Index"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Reordered Columns",{"Index"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",{{"Value.1", "Divisions"}, {"Value", "Departments"}})
in
    #"Renamed Columns"

Hi @NickolajJessen 

Thank you for your reply. This works to split the division and department.

PBIDEV_10_0-1660117984382.png

 

 

But I have a question. How can I assure that we choose the correct division for the department. how can I use the division and department tables vi have already? Because I dont know if the raw data comes in the sequence that department1 has division1 and so on.

 

regards

PP

  

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.