TRICKY - Move cell up in certain columns

[Anonymous]

How would you do the following in Power Query in Power BI. See start and end results in the Image below....

In a nutshall i need to shift the cell up in the last 3 columns and replace the null values with the real data which is sitting at the botttom..

[Syndicate_Admin]

Hello. Excuse me I have a similar case, but what I need is to move up the data in column2, so that they are confronted with the data in column1 in the image that I attach, how is it done?

[Kumail]

Hello @Anonymous 

You unpivot the table and remove null from the columns. 

If you could send a sample .pbix that demonstrates what you are looking to get. It would really help to provide you with a quick solution.

You can send the sample .pbix file by adding it to your drive or dropbox and add the link here. 

Regards
Kumail Raza

If this answers your question, mark it as solution.

Kudos are appreciated!!

[AlexisOlson]

There's is a .pbix link in this thread.

Here it is again:
https://www.dropbox.com/s/f10sso718xq6zt3/SampleFile.pbix?dl=0

[AlexisOlson]

Here's a very similar question I answered previously:

Move up values when null Power Query

The basic approach is to turn the table into a list of columns, remove the nulls from each, and then put it back together.

[Anonymous]

Hi Alexis - firstly thanks, how do you turn the table unto a list of column and can you kindly detail the steps as i found that solution but could not complete the execrise sucessfully to get the end result.

[AlexisOlson · Accepted solution]

There isn't a nice button in the ribbon to do this, so I go into the Advanced Editor to write the step needed.

Here's what the M code for that particular step look like (my StackOverflow answer):

Table.FromColumns(
    List.Transform(
        Table.ToColumns(#"Prev Step"),
        List.RemoveNulls
    ),
    Table.ColumnNames(#"Prev Step")
)

Now since you had "Null" instead of null in the file you shared, I had to add a step to replace those before doing the above-mentioned step.

Here's the whole query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUfLKz8gDUn6lOTloVKxOtJIRSEVibmoxbiXGQE5wYi5uBSZATkgmHgWmYEvwWGEG5HjjM8EcyPFJLErErQJVGOStcF8gYWCoD0RGBkaGWFSB3BUMU2WKSxXIbtdwqCoTXKosDEGe8IuEqrPAqQ6uylgfiHC5DOQBZ0cfqGnmuNShKoNZGgsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Name = _t, Profit = _t, State = _t, Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"Name", type text}, {"Profit", type text}, {"State", type text}, {"Date", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type","Null",null,Replacer.ReplaceValue,{"ID", "Name", "Profit", "State", "Date"}),
    RemoveNulls = Table.FromColumns(List.Transform(Table.ToColumns(#"Replaced Value"), List.RemoveNulls), Table.ColumnNames(#"Replaced Value"))
in
    RemoveNulls

[AlexisOlson]

If you post your data as a table I can copy and paste, I can demonstrate more easily.

[Anonymous]

HI Alexis

See Sample PBIX with data
https://www.dropbox.com/s/f10sso718xq6zt3/SampleFile.pbix?dl=0