Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello everyone,
I have an unstructured excel source and I need to transform it. Can anybody help me?
Here is the sample data:
Company/Feature/Project | Amount |
Company: Company A | 1600 |
Feature: Feature 1 | 1000 |
Project AAA | 200 |
Project BBB | 300 |
Project CCC | 500 |
Feature: Feature 2 | 600 |
Project AAA | 100 |
Project BBB | 200 |
Project CCC | 300 |
Company: Company B | 1000 |
Feature: Feature 1 | 1000 |
Project AAA | 200 |
Project BBB | 300 |
Project CCC | 500 |
Company: Company C | 1100 |
Feature: Feature 1 | 500 |
Project AAA | 200 |
Project BBB | 300 |
Feature: Feature 2 | 600 |
Project AAA | 100 |
Project BBB | 200 |
Project CCC | 300 |
This is the expected output:
Company | Feature | Project | Amount |
Company A | Feature 1 | Project AAA | 200 |
Company A | Feature 1 | Project BBB | 300 |
Company A | Feature 1 | Project CCC | 500 |
Company A | Feature 2 | Project AAA | 100 |
Company A | Feature 2 | Project BBB | 200 |
Company A | Feature 2 | Project CCC | 300 |
Company B | Feature 1 | Project AAA | 200 |
Company B | Feature 1 | Project BBB | 300 |
Company B | Feature 1 | Project CCC | 500 |
Company C | Feature 1 | Project AAA | 200 |
Company C | Feature 1 | Project BBB | 300 |
Company C | Feature 2 | Project AAA | 100 |
Company C | Feature 2 | Project BBB | 200 |
Company C | Feature 2 | Project CCC | 300 |
Thank you soo much!
Solved! Go to Solution.
let Source = Excel.Workbook(File.Contents("C:\Users\Steve James\Desktop\sample.xlsx"), null, true), Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data], #"Removed Blank Rows" = Table.SelectRows(Sheet1_Sheet, each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))), #"Removed Top Rows" = Table.Skip(#"Removed Blank Rows",1), #"Renamed Columns" = Table.RenameColumns(#"Removed Top Rows",{{"Column1", "Attributes"}, {"Column2", "Amount"}}), #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Company", each if Text.Start([Attributes],9)="Company: " then Text.AfterDelimiter([Attributes], ": ") else null), #"Filled Down" = Table.FillDown(#"Added Custom",{"Company"}), #"Filtered Rows" = Table.SelectRows(#"Filled Down", each not Text.StartsWith([Attributes], "Company: ")), #"Added Custom1" = Table.AddColumn(#"Filtered Rows", "Feature", each if Text.Start([Attributes],9) = "Feature: " then Text.AfterDelimiter([Attributes], ": ") else null), #"Filled Down1" = Table.FillDown(#"Added Custom1",{"Feature"}), #"Added Custom2" = Table.AddColumn(#"Filled Down1", "Project", each if Text.Start([Attributes],8)="Project " then [Attributes] else null), #"Filtered Rows1" = Table.SelectRows(#"Added Custom2", each ([Project] <> null)), #"Reordered Columns" = Table.ReorderColumns(#"Filtered Rows1",{"Attributes", "Company", "Feature", "Project", "Amount"}), #"Removed Columns" = Table.RemoveColumns(#"Reordered Columns",{"Attributes"}), #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"Company", type text}, {"Feature", type text}, {"Project", type text}, {"Amount", Int64.Type}}) in #"Changed Type"
let Source = Excel.Workbook(File.Contents("C:\Users\Steve James\Desktop\sample.xlsx"), null, true), Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data], #"Removed Blank Rows" = Table.SelectRows(Sheet1_Sheet, each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))), #"Removed Top Rows" = Table.Skip(#"Removed Blank Rows",1), #"Renamed Columns" = Table.RenameColumns(#"Removed Top Rows",{{"Column1", "Attributes"}, {"Column2", "Amount"}}), #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Company", each if Text.Start([Attributes],9)="Company: " then Text.AfterDelimiter([Attributes], ": ") else null), #"Filled Down" = Table.FillDown(#"Added Custom",{"Company"}), #"Filtered Rows" = Table.SelectRows(#"Filled Down", each not Text.StartsWith([Attributes], "Company: ")), #"Added Custom1" = Table.AddColumn(#"Filtered Rows", "Feature", each if Text.Start([Attributes],9) = "Feature: " then Text.AfterDelimiter([Attributes], ": ") else null), #"Filled Down1" = Table.FillDown(#"Added Custom1",{"Feature"}), #"Added Custom2" = Table.AddColumn(#"Filled Down1", "Project", each if Text.Start([Attributes],8)="Project " then [Attributes] else null), #"Filtered Rows1" = Table.SelectRows(#"Added Custom2", each ([Project] <> null)), #"Reordered Columns" = Table.ReorderColumns(#"Filtered Rows1",{"Attributes", "Company", "Feature", "Project", "Amount"}), #"Removed Columns" = Table.RemoveColumns(#"Reordered Columns",{"Attributes"}), #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"Company", type text}, {"Feature", type text}, {"Project", type text}, {"Amount", Int64.Type}}) in #"Changed Type"
Thank you soo much! This is what I'm looking for!
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
122 | |
101 | |
71 | |
61 |