cancel
Showing results for 
Search instead for 
Did you mean: 
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

 

 

And here the solution in Power Query (you can post it into the advanced editor and look throgh the steps):

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? Mark my post as a solution!

Proud to be a Super User!




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

 

 

And here the solution in Power Query (you can post it into the advanced editor and look throgh the steps):

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? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
Join Arun Ulag at MPPC23

Join Arun Ulag at MPPC23

Get a sneak peek into this year's Power Platform Conference Keynote.

PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Top Solution Authors
Top Kudoed Authors