cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
monty4u Frequent Visitor
Frequent Visitor

Moving certain rows in a column

Hi we received a huge text file from a user under which all columns where combined into one column. We are able to split them into 26 columns using the delimiter option.

But now I have the following situation. 

 

Column1 Column2  Column 3 Column 4 Column 5 

A               B              C              null          null 

D               E              F               null          null

G               0              0               H             I

 

We want to shift columns left for the rows where ever it is zero so that its aligned and then finally delete column 25 & 26.

 

Please let me know if you need additional context

1 ACCEPTED SOLUTION

Accepted Solutions
monty4u Frequent Visitor
Frequent Visitor

Re: Moving certain rows in a column

Alternatively created 6 custom columns to replace values with appropriate column values and finally hiding the erroneous columns did the trick, Not elegant but works for now.

3 REPLIES 3
Super User
Super User

Re: Moving certain rows in a column

Hello @monty4u 

 

I'm assuming your text file looked something like this.

 

A,B,C
D,E,F
G,0,0,H,I

 

Could you safely remove each instance of "0," in the string before you split it (replace "0," with null)?  Then your split would align correctly.

Highlighted
monty4u Frequent Visitor
Frequent Visitor

Re: Moving certain rows in a column

We already had a redundant text which I replaced with 0

monty4u Frequent Visitor
Frequent Visitor

Re: Moving certain rows in a column

Alternatively created 6 custom columns to replace values with appropriate column values and finally hiding the erroneous columns did the trick, Not elegant but works for now.

Helpful resources

Announcements
Community Highlights

Community Highlights

Find out what's new in the Power BI Community!

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 6 members 1,618 guests
Please welcome our newest community members: