Reply
Zulfi69
New Member

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.

5 REPLIES 5
v-karpurapud
Community Support
Community Support

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.

v-karpurapud
Community Support
Community Support

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.

v-karpurapud
Community Support
Community Support

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.

Omid_Motamedise
Super User
Super User

Please provide the sample data

v-karpurapud
Community Support
Community Support

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.

 

vkarpurapud_0-1738662139259.png

 

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!



 

  
 

avatar user

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

FebPBI_Carousel

Power BI Monthly Update - February 2025

Check out the February 2025 Power BI update to learn about new features.

Feb2025 NL Carousel

Fabric Community Update - February 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)