Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
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
Proud to be a Datanaut!
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.
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
Proud to be a Datanaut!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
102 | |
53 | |
21 | |
12 | |
12 |