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
Anonymous
Not applicable

Separating a Column of Data based on Keywords into smaller columns

So I have a bunch of really messy pdf files that i'm drawing data from and they have the data all in one column when pulled into power bi like this:

1.PNG

 

 

 

 

 

 

 

 

 

I want to reorgnaize the data into this format, summing the numbers that belong in the same category:

2.PNG

 

Any ideas on how to approach?

1 ACCEPTED SOLUTION
Cmcmahan
Resident Rockstar
Resident Rockstar

So I went through and used  power query to get a table that looks like this:

Column1Column1 - Copy
Build Hours300
Build Hours200
Demo Hours400
Other Hours200
Other Hours200
Other Hours100

 

Here's the power query (and I'm sure it could be cleaned up, but it gets a usuable result:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcirNzElR8MgvLSpWitWJVjI2MADT6OJGUHGX1Nx8JGETqLB/SUZqERblMNoQRMcCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Column1", "Column1 - Copy"),
    #"Remove Numbers" = Table.TransformColumns(#"Duplicated Column",{{"Column1", each Text.Combine(List.RemoveItems(Text.ToList(_),{"0".."9"}))}}),
    #"Replaced Value" = Table.ReplaceValue(#"Remove Numbers","",null,Replacer.ReplaceValue,{"Column1"}),
    #"Filled Down" = Table.FillDown(#"Replaced Value",{"Column1"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Filled Down",{{"Column1 - Copy", Int64.Type}}),
    #"Removed Errors" = Table.RemoveRowsWithErrors(#"Changed Type1", {"Column1 - Copy"})
in
    #"Removed Errors"

View solution in original post

1 REPLY 1
Cmcmahan
Resident Rockstar
Resident Rockstar

So I went through and used  power query to get a table that looks like this:

Column1Column1 - Copy
Build Hours300
Build Hours200
Demo Hours400
Other Hours200
Other Hours200
Other Hours100

 

Here's the power query (and I'm sure it could be cleaned up, but it gets a usuable result:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcirNzElR8MgvLSpWitWJVjI2MADT6OJGUHGX1Nx8JGETqLB/SUZqERblMNoQRMcCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Column1", "Column1 - Copy"),
    #"Remove Numbers" = Table.TransformColumns(#"Duplicated Column",{{"Column1", each Text.Combine(List.RemoveItems(Text.ToList(_),{"0".."9"}))}}),
    #"Replaced Value" = Table.ReplaceValue(#"Remove Numbers","",null,Replacer.ReplaceValue,{"Column1"}),
    #"Filled Down" = Table.FillDown(#"Replaced Value",{"Column1"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Filled Down",{{"Column1 - Copy", Int64.Type}}),
    #"Removed Errors" = Table.RemoveRowsWithErrors(#"Changed Type1", {"Column1 - Copy"})
in
    #"Removed Errors"

Helpful resources

Announcements
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!

Jan NL Carousel

Fabric Community Update - January 2025

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