Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
Ahh, i see.
I uploaded a .pbix here.
https://drive.google.com/drive/folders/1AjePk7NUXAloXHr42BEa2lDOr5dQr2Wg
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_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 |
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
Ahh, i see.
I uploaded a .pbix here.
https://drive.google.com/drive/folders/1AjePk7NUXAloXHr42BEa2lDOr5dQr2Wg
Thank you so much! Will this work with my original department table with 590 rows?
Only one way to find out 😉
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"
Thank you for your reply. This works to split the division and department.
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
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |