Deleting certain values in a column and shifting row data from right to left

[Mituka]

Hello All

I am quite new to power query editor and have a challenge with my data. I have some data logs I retrieve from the web that are comma separated values. Once imported to PowerBI some rows are not aligned correctly due to a comma separating some phone models. See the screenshot of what I mean in row 40 and 42.

I would like to delete any number values in the isMobile column and shift the contents on the RIGHT to the LEFT. The isMobile column has to contain only the True or False values but unfortunately, the logs are not consistent on particular phone models (Apple). Anyone know how to go about this in power query editor. I have previously had to do this manually or with macros in excel. However, I need power BI to automatically do this after importing realtime data.

[v-cherch-msft · Accepted solution]

Hi @Mituka

You may try to use 'Append Queries' and 'Text Filters' in Query Editor. Show the sample file as below:

https://www.dropbox.com/s/2czwvohjmkbl96l/Deleting%20certain%20values%20in%20a%20column%20and%20shif...

First, Remove the 'Column1' in Table.

Second, Copy the table as Table(2) and filter the table(2) and rename the columns as below:

Last, Append the Table Query and Table(2) Query as Append Table. Then filter again to get the table as requested.

Regards,

Cherie