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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anony_mous
Frequent Visitor

Cleaning data in power query of power bi

I have a table with 3 columns and I am bit confused with cleaning the data the dirty data is as follows:

Order ID           category                      Amount

CA-2011-167199.    Binders|Art.          5.4 | 31

CA-2011-149020.    Tables|Binders.      17 |13

CA-2011-127614.    Fasteners|Art.         6 | 3.6

 

After cleaning the data the data should look like this:

Order ID.                Category.                Amount

CA-2011-167199.   Binders.                   5.4

CA-2011-167199.    Art.                          31

CA-2011-149020.     Tables.                     17

CA-2011-149020.       Binders                   13

CA-2011-127614.       Fasteners                 6

CA-2011-127614.        Art.                         3.6

 

All suggestions are helpful thank you in advance 

1 ACCEPTED SOLUTION
tackytechtom
Super User
Super User

Hi @Anony_mous ,

 

How about this:

 

Before:

tackytechtom_1-1672348483117.png

 

After:

tackytechtom_0-1672348460248.png

 

 

Here the code in Power Query M that you can paste into the advanced editor (if you do not know, how to exactly do this, please check out this quick walkthrough)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Xcs7DoAgFETRrRhqJDwQySvRxBXYERs/iTYUqJ2Ld7QylidzJ0bRhtJoopJqT8xCimZL85L34ipCPmBXKw9YEoP85BVrozF3Z05PPG5pB41iwP1iS6wd1n6ZVszvB/TKQJVVyIcb", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Order ID" = _t, Category = _t, Amount = _t]),
    #"Split Column by Delimiter 1" = Table.SplitColumn(Source, "Category", Splitter.SplitTextByDelimiter(" | ", QuoteStyle.Csv), {"Category", "Category.2"}),
    #"Split Column by Delimiter 2" = Table.SplitColumn(#"Split Column by Delimiter 1", "Amount", Splitter.SplitTextByDelimiter(" | ", QuoteStyle.Csv), {"Amount", "Amount.2"}),
    #"Removed Other Columns 1" = Table.SelectColumns(#"Split Column by Delimiter 2",{"Order ID", "Category.2", "Amount.2"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns 1",{{"Category.2", "Category"}, {"Amount.2", "Amount"}}),
    #"Removed Other Columns 2" = Table.SelectColumns(#"Split Column by Delimiter 2",{"Order ID", "Category", "Amount"}),
    #"Appended Query" = Table.Combine({#"Removed Other Columns 2", #"Renamed Columns"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Appended Query",{{"Amount", type number}})
in
    #"Changed Type"

 

Let me know if this works for you 🙂

 

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/



Did I answer your question➡️ Please, mark my post as a solution ✔️

Also happily accepting Kudos 🙂

Feel free to connect with me on LinkedIn! linkedIn

#proudtobeasuperuser 

View solution in original post

1 REPLY 1
tackytechtom
Super User
Super User

Hi @Anony_mous ,

 

How about this:

 

Before:

tackytechtom_1-1672348483117.png

 

After:

tackytechtom_0-1672348460248.png

 

 

Here the code in Power Query M that you can paste into the advanced editor (if you do not know, how to exactly do this, please check out this quick walkthrough)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Xcs7DoAgFETRrRhqJDwQySvRxBXYERs/iTYUqJ2Ld7QylidzJ0bRhtJoopJqT8xCimZL85L34ipCPmBXKw9YEoP85BVrozF3Z05PPG5pB41iwP1iS6wd1n6ZVszvB/TKQJVVyIcb", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Order ID" = _t, Category = _t, Amount = _t]),
    #"Split Column by Delimiter 1" = Table.SplitColumn(Source, "Category", Splitter.SplitTextByDelimiter(" | ", QuoteStyle.Csv), {"Category", "Category.2"}),
    #"Split Column by Delimiter 2" = Table.SplitColumn(#"Split Column by Delimiter 1", "Amount", Splitter.SplitTextByDelimiter(" | ", QuoteStyle.Csv), {"Amount", "Amount.2"}),
    #"Removed Other Columns 1" = Table.SelectColumns(#"Split Column by Delimiter 2",{"Order ID", "Category.2", "Amount.2"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns 1",{{"Category.2", "Category"}, {"Amount.2", "Amount"}}),
    #"Removed Other Columns 2" = Table.SelectColumns(#"Split Column by Delimiter 2",{"Order ID", "Category", "Amount"}),
    #"Appended Query" = Table.Combine({#"Removed Other Columns 2", #"Renamed Columns"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Appended Query",{{"Amount", type number}})
in
    #"Changed Type"

 

Let me know if this works for you 🙂

 

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/



Did I answer your question➡️ Please, mark my post as a solution ✔️

Also happily accepting Kudos 🙂

Feel free to connect with me on LinkedIn! linkedIn

#proudtobeasuperuser 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors