Cleaning Messy Data - Single Column - Many Rows with different headers -

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!