Easiest way to treat/clean data for micro biology containing "<" sign?

[Irwin]

Hi,

I have a large dataset with many columns containing micro biological data.

Many of these columns will have a value of <1000 or <100. For example. Enterobaceriacea <10.

Powerbi cannot handle a less than value when the other values are whole numbers. So I have to split my columns into:

Column A, Column B

Column A would then be "attribute": <

Column B would be the whole number.

This way I can later create a measures that uses the value and checks column A to be blank/not blank.

However, I find it quite a big amount of work to go through 20+ columns in power query and split them.

The method I use now are as follows:

= Table.AddColumn(#"Removed Columns", "Custom", each if Text.Contains([Enterobacteriacea], "<" ) then "<" else [Enterobacteriacea])

And then 

= Table.AddColumn(#"Changed Type", "Custom", each Text.Select([TPC], {"0".."9"}))

After this I have to remove the original column, rename the new column and change the type to whole number.

Doing this x 20 seems stupid to me. Is there a better solution to split all columns that has "<" into Attribute columns and whole number coloumns?

Thank you for any assistance. 🙂

[v-shex-msft]

Hi @Irwin,

Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng

[Irwin]

Sure here is some example data. Imagine this with 20 ish columns.

As is:

TPC	Entero-bacteriaceae	Bacillus cereus	Yeast/
Mould
<1000	<10	<10	<10
<1000	<10	<10	<10
<1000	<10	10	<10
<1000	<10	<10	<10
<1000	<10	<10	<10
<1000	<10	<10	<10
<1000	<10	<10	<10
1000	<10	<10	<10
<1000	<10	10	<10
<1000	<10	<10	10
<1000	<10	<10	<10
<1000	<10	<10	<10
<1000	<10	<10	<10
<1000	10	<10	<10
<1000	<10	<10	<10
<1000	<10	<10	<10
<1000	20	<10	<10
<1000	<10	<10	<10
<1000	<10	<10	30
<1000	<10	<10	<10
<1000	<10	<10	<10
<1000	<10	<10	<10
10000	<10	<10	<10
<1000	20	<10	10
<1000	<10	<10	<10
<1000	<10	<10	<10
<1000	200	<10	<10
1000	<10	<10	<10
<1000	<10	<10	<10
<1000	<10	<10	<10
<1000	<10	<10	<10
<1000	<10	<10	<10
<1000	<10	<10	<10
<1000	<10	<10	<10
<1000	<10	<10	<10

To be

TPC Attribute	TPC	EB Attribute	Entero-bacteriaceae	BC Attribute	Bacillus cereus	Y/M Attribute	Yeast/Mould
<	1000	<	10	<	10	<	10
<	1000	<	10	<	10	<	10
<	1000	<	10		10	<	10
<	1000	<	10	<	10	<	10
<	1000	<	10	<	10	<	10
<	1000	<	10	<	10	<	10
<	1000	<	10	<	10	<	10
	1000	<	10	<	10	<	10
<	1000	<	10		10	<	10
<	1000	<	10	<	10	<	10
<	1000	<	10	<	10	<	10
<	1000	<	10	<	10	<	10
<	1000	<	10	<	10	<	10
<	1000		10	<	10	<	10
<	1000	<	10	<	10	<	10
<	1000	<	10	<	10	<	10
<	1000		20	<	10	<	10
<	1000	<	10	<	10	<	10
<	1000	<	10	<	10		30
<	1000	<	10	<	10	<	10
<	1000	<	10	<	10	<	10
<	1000	<	10	<	10	<	10
	10000	<	10	<	10	<	10
<	1000		20	<	10		10
<	1000	<	10	<	10	<	10
<	1000	<	10	<	10	<	10
<	1000		200	<	10	<	10
	1000	<	10	<	10	<	10
<	1000	<	10	<	10	<	10
<	1000	<	10	<	10	<	10
<	1000	<	10	<	10	<	10
<	1000	<	10	<	10	<	10
<	1000	<	10	<	10	<	10
<	1000	<	10	<	10	<	10
<	1000	<	10	<	10	<	10

[v-shex-msft · Accepted solution]

Hi @Irwin,

Here is a custom M query function to add placeholder into the records if it does not include "<" character, you can use it to add whitespace to number values and them you can do ‘split column’ by number of characters.

#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Function.Invoke((x)=> Record.FromList( List.Transform(Record.ToList(x), each if Text.Contains(_,"<") then _ else " "&_),Record.FieldNames(_)),{_}))

Full query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wiik1MDBONjQwMFDSgXNwMGN1qKeediYPlPoBCpNBGhoo6gePS4zo7BLjwRk7IJXUCL6BiULa5sJR9WSojwUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [TPC = _t, #"Entero-bacteriaceae" = _t, #"Bacillus cereus" = _t, #"Yeast/Mould" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"TPC", type text}, {"Entero-bacteriaceae", type text}, {"Bacillus cereus", type text}, {"Yeast/Mould", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Function.Invoke((x)=> Record.FromList( List.Transform(Record.ToList(x), each if Text.Contains(_,"<") then _ else " "&_),Record.FieldNames(_)),{_})),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"TPC", "Entero-bacteriaceae", "Bacillus cereus", "Yeast/Mould"}),
    #"Expanded Custom" = Table.ExpandRecordColumn(#"Removed Columns", "Custom", {"TPC", "Entero-bacteriaceae", "Bacillus cereus", "Yeast/Mould"}, {"TPC", "Entero-bacteriaceae", "Bacillus cereus", "Yeast/Mould"})
in
    #"Expanded Custom"

Regards,

Xiaoxin Sheng

[Irwin]

Excellent. I will  try it out when I get time on work. Thank you. 🙂