Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?
Solved! Go to 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.
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
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.
Hi @Schwinn123 ,
If the cycle time is just null, we can use the Fill down function as following
If it is zero, we can use the Replace Value to replace it first, then fill down as previous.
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.
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.
Hi @v-lid-msft
We are much much closer to the goals, But we still need a little improvment.
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.
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
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.
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]
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.
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |