Forum Discussion
IF and Offset in Power Query
Hi Team, I need to use IF and OFFSET to get the desired result. Im struggling to get the output since im a intermediate user in power query could you team suggest the solution. I'm new to power qu...
Hi rajrajsha
please paste the following code into the advanced editor and follow the steps. Also, next time when you get an error, please post the error message, as this is usually needed to find out what's going wrong.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nZVNbttADIWvYnidQvyd4cwl2n2QRRZZFAjaogh6/pKjkSzJViJoYUAm4PeZ75HU8/OVk16frgiFiVglnmlAHgjQLkgVaFNKVdhLP35/vP36+Pn6fvn+7+3v++sfr7nWN2GRHD9pn5enrwnE94RygEAzQcpISLmQpUYH1xrlrEJZV4ir7gFcygFcuLdAXwMIKvA5As0EJLOmkTlBGR2kASZLtKpuSlaFdhAhFjbxFAQfYbggnmTQjcHarRIzIRl9gdIFpVLelHR07yHDxcIro96HHGHoHMeyJEcYdGOYJf9SijFDQ6QBpadLVdO64nnbHsCVHEDjFvhH1wAsSs3lNcDuAHgEQDPAlw3HvYMsBj1aTN0SGC1ZlqRK2mGEWKSt0JtIRxhd8AyDZgYDN+cQVJPmeAYcIIdguUAaJ2pR8pOCee9+uFgw8jRR+QDDvWpn6wyDbgzlyLQ0o1r2oLdNw9raflB6iHCtiJymM2XnEJ91sUTQjBDCFiloNsTcRnggnYeqXe5FBSvvbV5IxeYl602UewJk2BIU7gh792NFoJmgmNoYAxgVsfjHnqtP6WRKW75lSSrudRFi4dP8xkA4CaH9G7KC0ALCEPkVwyLU5i3StS6YK29Lnxz00GqBT3Ggv1xf/gM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Store Number" = _t, WO = _t, #"Date/Time In" = _t, #"Date/Time Out" = _t, #"Potential Status" = _t, Product = _t, RunningCount = _t, Flags = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Store Number", Int64.Type}, {"WO", Int64.Type}, {"Date/Time In", type datetime}, {"Date/Time Out", type datetime}, {"Potential Status", type text}, {"Product", type text}, {"RunningCount", Int64.Type}, {"Flags", Int64.Type}}, "en-us"),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Store Number"}, {{"Partition", each Table.AddIndexColumn(Table.Buffer(Table.Sort(_, {"Date/Time In"})), "RunningCountImke",1,1) }}),
#"Expanded Partition" = Table.ExpandTableColumn(#"Grouped Rows", "Partition", {"WO", "Date/Time In", "Date/Time Out", "Potential Status", "Product", "RunningCount", "Flags", "RunningCountImke"}, {"WO", "Date/Time In", "Date/Time Out", "Potential Status", "Product", "RunningCount", "Flags", "RunningCountImke"})
in
#"Expanded Partition"
Thanks for your good heart mam. That code worked fine with the sample date which i sent but it is not working on the other data that i have which is showing null. Im sending the complete code with what you have worked. Please kindly review once if you have time.
let
Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Store Number", Int64.Type}, {"WO", Int64.Type}, {"Date/Time In", type datetime}, {"Date/Time Out", type datetime}, {"Potential Status", type text}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Potential Status] = "Potential Overlap")),
#"Duplicated Column" = Table.DuplicateColumn(#"Filtered Rows", "Date/Time In", "Date/Time In - Copy"),
#"Pull only dates from In" = Table.TransformColumns(#"Duplicated Column",{{"Date/Time In - Copy", DateTime.Date, type date}}),
#"Date to Number" = Table.TransformColumnTypes(#"Pull only dates from In",{{"Date/Time In - Copy", Int64.Type}}),
#"Duplicated Column1" = Table.DuplicateColumn(#"Date to Number", "Store Number", "Store Number - Copy"),
#"Reordered Columns" = Table.ReorderColumns(#"Duplicated Column1",{"Store Number", "WO", "Date/Time In", "Date/Time Out", "Potential Status", "Store Number - Copy", "Date/Time In - Copy"}),
#"Inserted Merged Column" = Table.AddColumn(#"Reordered Columns", "Merged", each Text.Combine({Text.From([#"Store Number - Copy"], "en-US"), Text.From([#"Date/Time In - Copy"], "en-US")}, "-"), type text),
#"Removed Columns" = Table.RemoveColumns(#"Inserted Merged Column",{"Store Number - Copy", "Date/Time In - Copy"}),
#"Added Index" = Table.AddIndexColumn(#"Removed Columns", "Index", 1, 1),
#"Reordered Columns1" = Table.ReorderColumns(#"Added Index",{"Store Number", "WO", "Date/Time In", "Date/Time Out", "Potential Status", "Index", "Merged"}),
#"Renamed Columns" = Table.RenameColumns(#"Reordered Columns1",{{"Index", "Transaction No"}, {"Merged", "Product"}}),
BufferedTable = Table.Buffer(#"Renamed Columns"),
#"Added Custom" = Table.AddColumn(BufferedTable, "RunningCount", (OT) => Table.RowCount(Table.SelectRows(BufferedTable, (IT) => IT [Transaction No] <= OT[Transaction No] and IT[Product] = OT[Product]))),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"RunningCount", Int64.Type}}),
#"Removed Trans No" = Table.RemoveColumns(#"Changed Type1",{"Transaction No"}),
#"Grouped Rows" = Table.Group(#"Removed Trans No", {"Store Number"}, {{"Partition", each Table.AddIndexColumn(Table.Buffer(Table.Sort(_, {"Date/Time In"})), "RunningCountImke",1,1) }}),
#"Expanded Partition" = Table.ExpandTableColumn(#"Grouped Rows", "Partition", {"WO", "Date/Time In", "Date/Time Out", "Potential Status", "Product", "RunningCount", "Flags", "RunningCountImke"}, {"WO", "Date/Time In", "Date/Time Out", "Potential Status", "Product", "RunningCount", "Flags", "RunningCountImke"})
in
#"Expanded Partition"