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

How to copy the value between rows according to some conditions

I have a table on the left in power query.   I want to copy the value [cycle time], according to the [Staion], if the station has a underscore sign, Station 123_1's cycle time MUST have a value, and 123_2 or _3's cycle time MUST be 0. In excel I can simply drag down the cycle time to a certain row, but in power query is not possible to do drag down.  How can I do this in PQ?

Annotation 2019-11-04 180822.jpg

@v-shex-msft 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: How to copy the value between rows according to some conditions

Hi @Schwinn123 ,

 

We can use one query for each column

 

FillForCycleTime = Table.RenameColumns(
                            Table.RemoveColumns(
                                Table.FillDown(
                                    Table.AddColumn(#"Changed Type", "temp", each if [cycle time] = 0 and Text.Contains([Station],"_") and not Text.Contains([Station],"_1") then null else [cycle time])
                                ,{"temp"})
                            ,{"cycle time"})
                        ,{{"temp", "cycle time"}})

 

Add all the query into the Advanced Editor, Replace the Blue and Red Part as column name , Change the Green one as name of previous step. Also do not forget the comma at the end of part unless it is the last step.

 

10.PNG

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyjjdU0lEyBmIjAyBhoBSrAxE2ggqjihpjihqCDABjUxABETUyMkIWhQgaGxujK40FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Station = _t, #"Station number" = _t, #"cycle time" = _t, #"Another Column" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Station number", Int64.Type}, {"cycle time", Int64.Type}}),
    FillForCycleTime = Table.RenameColumns(
                            Table.RemoveColumns(
                                Table.FillDown(
                                    Table.AddColumn(#"Changed Type", "temp", each if [cycle time] = 0 and Text.Contains([Station],"_") and not Text.Contains([Station],"_1") then null else [cycle time])
                                ,{"temp"})
                            ,{"cycle time"})
                        ,{{"temp", "cycle time"}}),
    FillForAnotherColumn = Table.RenameColumns(
                            Table.RemoveColumns(
                                Table.FillDown(
                                    Table.AddColumn(FillForCycleTime, "temp", each if [Another Column] = 0 and Text.Contains([Station],"_") and not Text.Contains([Station],"_1") then null else [Another Column])
                                ,{"temp"})
                            ,{"Another Column"})
                        ,{{"temp", "Another Column"}})


in
    FillForAnotherColumn

 

11.PNG12.PNG

 

Sorry but we did not find a effective way of  loop all the column using list, and that will be hard to understand and maintain.

 


BTW, pbix as attached.

 

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

6 REPLIES 6
Community Support Team
Community Support Team

Re: How to copy the value between rows according to some conditions

Hi @Schwinn123 ,

 

If the cycle time is just null, we can use the Fill down function as following

 

2.PNG3.PNG4.PNG

 

If it is zero, we can use the Replace Value to replace it first, then fill down as previous.

 

7.PNG8.PNG9.PNG

 


BTW, pbix as attached.

 

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Schwinn123 Frequent Visitor
Frequent Visitor

Re: How to copy the value between rows according to some conditions

Hi Dong Really appreciate your reply, But there is another issue I forgot to metion. There are also have some other stations' the cycle time could also be 0 If we replace the value to "null", which will lead these stations value will be “null” as well. The only condition that we can judge the cycle time need to“fill down” is according to the [Station] to see if it has a "_" sign, if yes, All 123_2 123_3 cycle time will follow 123_1 cycle time. If no the cycle time will remain its value.
Community Support Team
Community Support Team

Re: How to copy the value between rows according to some conditions

Hi @Schwinn123 ,

 

We can create a custom column with following formula instead of replace the origin column, then we can fill down in the new column.

 

if [cycle time] = 0 and Text.Contains([Station],"_") then null else [cycle time]

 

11.PNG

 


BTW, pbix as attached.

 

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Community Support Team
Community Support Team

Re: How to copy the value between rows according to some conditions

Hi @Schwinn123 ,


How about the result after you follow the suggestions mentioned in my original post?Could you please provide more details about it If it doesn't meet your requirement?

 

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Schwinn123 Frequent Visitor
Frequent Visitor

Re: How to copy the value between rows according to some conditions

Hi  @v-lid-msft 

We are much much closer to the goals, But we still need a little improvment.

  1.  For adding a custom column, the logic should be if the balance name contains _2,_3,_4....etc. should be null.  which means balance name contains _1 or doesn't contain “_”should also keep the original data.   Now all _1 stations' cycle time are changed if the value is 0, which should be remain as 0.
  2. For replaced value,  the logic should be only replaced the balance name contains _2,_3,_4....etc, I saw all 0 value replaced as null that is not correct.
  3. I have got 22 colums need do the same process as you said, but this will increase 22 addtional colums in power query. I have got no problem to do this since it will not take too long, I just wonder if there is any other way to be more efficient? this is just a curiosity   😀 for the improment of my power BI skills.

 

Community Support Team
Community Support Team

Re: How to copy the value between rows according to some conditions

Hi @Schwinn123 ,

 

We can use one query for each column

 

FillForCycleTime = Table.RenameColumns(
                            Table.RemoveColumns(
                                Table.FillDown(
                                    Table.AddColumn(#"Changed Type", "temp", each if [cycle time] = 0 and Text.Contains([Station],"_") and not Text.Contains([Station],"_1") then null else [cycle time])
                                ,{"temp"})
                            ,{"cycle time"})
                        ,{{"temp", "cycle time"}})

 

Add all the query into the Advanced Editor, Replace the Blue and Red Part as column name , Change the Green one as name of previous step. Also do not forget the comma at the end of part unless it is the last step.

 

10.PNG

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyjjdU0lEyBmIjAyBhoBSrAxE2ggqjihpjihqCDABjUxABETUyMkIWhQgaGxujK40FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Station = _t, #"Station number" = _t, #"cycle time" = _t, #"Another Column" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Station number", Int64.Type}, {"cycle time", Int64.Type}}),
    FillForCycleTime = Table.RenameColumns(
                            Table.RemoveColumns(
                                Table.FillDown(
                                    Table.AddColumn(#"Changed Type", "temp", each if [cycle time] = 0 and Text.Contains([Station],"_") and not Text.Contains([Station],"_1") then null else [cycle time])
                                ,{"temp"})
                            ,{"cycle time"})
                        ,{{"temp", "cycle time"}}),
    FillForAnotherColumn = Table.RenameColumns(
                            Table.RemoveColumns(
                                Table.FillDown(
                                    Table.AddColumn(FillForCycleTime, "temp", each if [Another Column] = 0 and Text.Contains([Station],"_") and not Text.Contains([Station],"_1") then null else [Another Column])
                                ,{"temp"})
                            ,{"Another Column"})
                        ,{{"temp", "Another Column"}})


in
    FillForAnotherColumn

 

11.PNG12.PNG

 

Sorry but we did not find a effective way of  loop all the column using list, and that will be hard to understand and maintain.

 


BTW, pbix as attached.

 

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Helpful resources

Announcements
New Ranks and Rank Icons in 2020

New Ranks and Rank Icons in 2020

Read the announcement for more information!

New Kudos Given Badges Coming

New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

November 2019 Community Highlights

November 2019 Community Highlights

Get an overview of the events and great community content from November.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)