Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

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!



 

  
 

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!

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Top Solution Authors
Top Kudoed Authors