PQ Sort Error

[RattlingCarp3048]

I've been stuck on this for a while. it seems so simple but i just cannot get pass it. In the PQ Editor, column A was/is a custom column formatted as text and is an alpha-numeric set of data. When i load the data in excel there are no blanks or errors and sorts perfectly but for some reason column A will not sort in the editor ascending or descending. I tried removing blanks and removing errors just in case i missed something but it still will not sort. The source data was copy/pasted so there are no additional connections or queries. Any ideas???

 

[johnny51981]

Is the original source of the "Accession # & Block" column formatted correctly from source to "Accession #" usage?

I would suggest the following:
1. Make sure the original column that is brought in from your source file is formatted to Text.
2. In this Accession # column, you may need to make sure to keep your text a text as it may be changing the format when you perform that Text.Start.
Before

=if Text.Contains([#"Accession # & Block"]," ") then Text.Start([#"Accession # & Block"], Text.PositionOf([#"Accession # & Block"]," ")) else [#"Accession # & Block"]

After

=if Text.Contains([#"Accession # & Block"]," ") then Text.Start([#"Accession # & Block"], Number.ToText(Text.PositionOf([#"Accession # & Block"]," "))) else [#"Accession # & Block"]

3. Once you complete the updating of the update of the Accession # Custom Column, make sure that at the end in the formula bar at the top of PQ Edition with ", type text". So your whole line of M Code should read like this

= Table.AddColumn(#"Previous Step", "Accession #", each if Text.Contains([#"Accession # & Block"]," ") then Text.Start([#"Accession # & Block"], Number.ToText(Text.PositionOf([#"Accession # & Block"]," "))) else [#"Accession # & Block"], type text)

4. Then try out your sort.

 

[RattlingCarp3048]

Is the original source of the "Accession # & Block" column formatted correctly from source to "Accession #" usage?

I would suggest the following:
1. Make sure the original column that is brought in from your source file is formatted to Text.
2. In this Accession # column, you may need to make sure to keep your text a text as it may be changing the format when you perform that Text.Start.
Before

=if Text.Contains([#"Accession # & Block"]," ") then Text.Start([#"Accession # & Block"], Text.PositionOf([#"Accession # & Block"]," ")) else [#"Accession # & Block"]

After

=if Text.Contains([#"Accession # & Block"]," ") then Text.Start([#"Accession # & Block"], Number.ToText(Text.PositionOf([#"Accession # & Block"]," "))) else [#"Accession # & Block"]

3. Once you complete the updating of the update of the Accession # Custom Column, make sure that at the end in the formula bar at the top of PQ Edition with ", type text". So your whole line of M Code should read like this

= Table.AddColumn(#"Previous Step", "Accession #", each if Text.Contains([#"Accession # & Block"]," ") then Text.Start([#"Accession # & Block"], Number.ToText(Text.PositionOf([#"Accession # & Block"]," "))) else [#"Accession # & Block"], type text)

4. Then try out your sort.

thank you so much. this seemed to have worked

 

[johnny51981]

Huzzah!!