Forum

Move Data from one ...
 
Notifications
Clear all

Move Data from one column to another column in PQ

8 Posts
2 Users
0 Reactions
488 Views
(@cmckeever)
Posts: 38
Trusted Member
Topic starter
 

I think I have attached the file.

In the file, I want to move the text(string) data only in the column City State Zip to the "Voter" column but leave the numerical value in the City State Zip column.

The City State Zip column sometimes contains numerical and text data and sometimes only text data.  I only want to move the data when there is both numerical data and text data.

In this particular case (there is 3,000 rows) there is always a space between the numbers and text, but the text and numbers will be different in other rows.

Just a side, every other City State Zip row is the data "Date:".  

In this case the problem text is SIMONS WAY, but it could be PINE RD or something.

I was too lazy to submit a bigger file because the first (despite the column name) are people's names.  If you need it I'll make up another longer file.  If there are any other questions, please let me know.

Cedric McKeever

 
Posted : 17/02/2024 10:50 pm
Riny van Eekelen
(@riny)
Posts: 1191
Member Moderator
 

It would be helpful if you could upload a slightly bigger file with more examples of what you are dealing with and indicating how the end result should look like.

 
Posted : 18/02/2024 2:37 am
(@cmckeever)
Posts: 38
Trusted Member
Topic starter
 

Hi,

Thanks for answering my question.  

The table was downloaded from a PDF with 2900 rows, and some of the columns got mixed up.  There are numerous problems with this download, however, the one I asked about is noted on lines 6, 7 and 22.  I wanted to move the street name only to the next column “Street,” and leave the number in the “Street No.” column. 

In this particular problem the number always comes first followed by a space, but as you will see there is more than one space in the cell.

I spent four days with ChatGPT4 and it moved the street name to a conditional column, but it couldn’t move the street name to the “Street” column.  I’m presently searching Google and Edge for a solution but no luck so far.  I think I have included the code where ChatGPT4 created a custom column and separated the names from the numerical data.  It should be noted that this code includes the separating of the person’s name in line 24.  That must go into a different column (“Voter Name”) and has different circumstances (number AFTER name).

I included the code, but I don’t think it will help because the column names are different.  If you have any questions, I’ll be glad to try to answer them.  I hope that I have attached the file correctly.

 

CODE

 

Records = Table.ToRecords(#"Added Custom to Flag"),  //previous step

   ExpandedRecords = List.Accumulate(Records, {}, (state, current) =>

    let

        newState = state & {current},

       additionalState = if current[Insert Empty Row] then

    let

        newRow = Record.FromList(

            {null, null, "Dates:", "Registered:", null, "Last Changed:", null, null, "Last Voted:", null, null, null, null},

            {"ID Number", "Voter Name", "Voter", "Column1", "Column2", "Column3", "Column4", "Column5", "Address", "City State Zip", "Moved", "Index", "Insert Empty Row"}

        )

    in

        newState & {newRow}

    else newState

in

    additionalState),

    TableFromRecords = Table.FromRecords(ExpandedRecords),

    // Function to check if a string starts with a number

    StartsWithNumber = (text as text) as logical =>

        let

            firstChar = Text.Start(text, 1),

            isNumber = List.Contains({"0".."9"}, firstChar)

        in

            isNumber,

 

    // Function to extract text part from a string

    ExtractText = (text as text) as text =>

        let

            numericChars = {"0".."9"},

            textPart = Text.Remove(text, numericChars)

        in

            textPart,

 

    // Add a column to determine if "Voter" starts with a number

    // Assuming 'PreviousStep' is the name of the step before this

    AddStartsWithNumber = Table.AddColumn(TableFromRecords, "StartsWithNumber", each StartsWithNumber([Voter])),

    //CheckAddStartsWithNumber = Table.Preview(AddStartsWithNumber),

 

    ExtractTextPart = Table.AddColumn(AddStartsWithNumber, "TextPart", each ExtractText([Voter])),

 
Posted : 19/02/2024 10:52 am
Riny van Eekelen
(@riny)
Posts: 1191
Member Moderator
 

Please have a look and let me know the attached file including a query does what you need. I'm insecure about what I highlighted in red on row 26. Is that also a problem in your data file or is it just a typo? 

 
Posted : 19/02/2024 12:07 pm
(@cmckeever)
Posts: 38
Trusted Member
Topic starter
 

Nice job,

To answer your question, that is not an error, it is also a problem to be dealt with. 

As you can see in the Results table, that has to be moved to the "Name" column and the number has to remain in that column.  It looks like you can adapt your code to attack that, if you want

Another problem is lines 1 and 2.  The "PO" line from line 1 of the "City State Zip column has to be moved to the end of line 1 of the "Street" column.  Then the 362 on line 2 of the "Street No." column has to be moved to the end of line one of the "Street" column, then delete row 2.  That is the only occurrence so I can fix that manually. 

Thanks for your help.

Cedric

 
Posted : 19/02/2024 7:05 pm
Riny van Eekelen
(@riny)
Posts: 1191
Member Moderator
 

Ooops, I didn't notice the issue on rows 1 and 2. That's a difficult one to automate, though. 

 
Posted : 20/02/2024 3:35 am
(@cmckeever)
Posts: 38
Trusted Member
Topic starter
 

I appreciate your help.  You do a great job on here.

There were so many problems with the PDF download into Excel that I decided to fix the rest manually.  Next time I'll ask for a digital copy of the information.

How is the winter in Sweden?

 

Cedric

 
Posted : 20/02/2024 9:47 am
Riny van Eekelen
(@riny)
Posts: 1191
Member Moderator
 

"How is the winter in Sweden?"

Cold and much snow!

 
Posted : 20/02/2024 11:51 am
Share: