Forum Discussion
Cleaning Messy Data - Single Column - Many Rows with different headers -
I am attaching a set of sample data. I need help to transform the data in a single single column with over 2000 rows of data. The rows have differnet information such as Name of the Executive, Customer code, and the dates. I want to name, code and dates in three seperate columns. Please show me how I can do it in power query.
Hi Zulfi69
Thank you for reaching out to the Microsoft Fabric Community Forum.To convert messy data into a structured table with columns for Executive Name, Customer Code, and Date, please follow these steps in Power Query:
1. Import the dataset into Power Query and add an index column to maintain the row sequence.
2. Create a new "Category" column to classify rows into:
- Executive Name: Rows with spaces in text.
- Customer Code: Numeric rows.
- Date: Rows with a date format.
3. Pivot the "Category" column to create separate columns (Executive Name, Customer Code, Date).
4. Apply the Fill Down operation to populate missing values in each column.
5. Remove unnecessary rows and sort the data.
Below is the complete Power Query M code for your reference. To use this code, go to the Home tab in Power Query and select 'Advanced Editor'.
let
Source = Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content]
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
#"AddIndex" = Table.AddIndexColumn(Changed Type, "Index", 1, 1, Int64.Type),
AddCategory = Table.AddColumn(AddIndex, "Category", each
if Text.Contains([Column1], " ") then "Executive Name"
else if Text.Middle([Column1], 2, 1) = "/" then "Date"
else if Value.Is(Value.FromText([Column1]), type number) then "Customer Code"
else "Unknown"
),
PivotTable = Table.Pivot(AddCategory, List.Distinct(AddCategory[Category]), "Category", "Column1"),
FillDown = Table.FillDown(PivotTable, {"Executive Name", "Customer Code", "Date"}),
RemoveNulls = Table.SelectRows(FillDown, each [Customer Code] <> null and [Date] <> null),
GroupRows = Table.Group(RemoveNulls, {"Executive Name"}, {
{"Customer Code", each List.First(Table.Column(_, "Customer Code")), type text},
{"Date", each List.First(Table.Column(_, "Date")), type text}
}),
SortedTable = Table.Sort(GroupRows, {{"Executive Name", Order.Ascending}})
in
SortedTable
I have attached a snapshot of the required output based on sample data below for your reference. Kindly confirm if this meets your requirements.If my response has resolved your query, please mark it as the Accepted Solution to help others. Additionally, I would appreciate a 'Kudos' if you found my response helpful.
Thank you!
5 Replies
Hi Zulfi69
Thank you for reaching out to the Microsoft Fabric Community Forum.To convert messy data into a structured table with columns for Executive Name, Customer Code, and Date, please follow these steps in Power Query:
1. Import the dataset into Power Query and add an index column to maintain the row sequence.
2. Create a new "Category" column to classify rows into:
- Executive Name: Rows with spaces in text.
- Customer Code: Numeric rows.
- Date: Rows with a date format.
3. Pivot the "Category" column to create separate columns (Executive Name, Customer Code, Date).
4. Apply the Fill Down operation to populate missing values in each column.
5. Remove unnecessary rows and sort the data.
Below is the complete Power Query M code for your reference. To use this code, go to the Home tab in Power Query and select 'Advanced Editor'.
let
Source = Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content]
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
#"AddIndex" = Table.AddIndexColumn(Changed Type, "Index", 1, 1, Int64.Type),
AddCategory = Table.AddColumn(AddIndex, "Category", each
if Text.Contains([Column1], " ") then "Executive Name"
else if Text.Middle([Column1], 2, 1) = "/" then "Date"
else if Value.Is(Value.FromText([Column1]), type number) then "Customer Code"
else "Unknown"
),
PivotTable = Table.Pivot(AddCategory, List.Distinct(AddCategory[Category]), "Category", "Column1"),
FillDown = Table.FillDown(PivotTable, {"Executive Name", "Customer Code", "Date"}),
RemoveNulls = Table.SelectRows(FillDown, each [Customer Code] <> null and [Date] <> null),
GroupRows = Table.Group(RemoveNulls, {"Executive Name"}, {
{"Customer Code", each List.First(Table.Column(_, "Customer Code")), type text},
{"Date", each List.First(Table.Column(_, "Date")), type text}
}),
SortedTable = Table.Sort(GroupRows, {{"Executive Name", Order.Ascending}})
in
SortedTable
I have attached a snapshot of the required output based on sample data below for your reference. Kindly confirm if this meets your requirements.If my response has resolved your query, please mark it as the Accepted Solution to help others. Additionally, I would appreciate a 'Kudos' if you found my response helpful.
Thank you!
Please provide the sample data
Hi Zulfi69
May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.
Thank you.
Hello Zulfi69
Hope you are doing well!
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.Hello Zulfi69
I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.
Thank you.
