Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Schwinn123
Helper I
Helper I

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

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
v-lid-msft
Community Support
Community Support

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.

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.

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.

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.

 

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.

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.