IF and Offset in Power Query

[rajrajsha]

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 query and this forum has been very helpful.

So below is an example I need and how could I do the same thing in power query?

I have included the image and sample date. Thank you soo much for the help.

[Greg_Deckler]

Not entirely certain what you are going for here but maybe @ImkeF or @edhans can riddle it out.

[ImkeF]

Hi @rajrajsha 

that looks like a nested index to me (after you've sorted the data by date, if necessary).

Please check this video: https://www.youtube.com/watch?v=-3KFZaYImEY 

If you have to sort, please make sure to use a buffer: https://community.powerbi.com/t5/Community-Blog/Bug-warning-for-Table-Sort-and-removing-duplicates-i... 

If you cannot figure it out by yourself, please post new sample data with correct headers like described here: https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-... 

[rajrajsha]

Hi ImKeF, Thanks for reviewing my query, I have tried with Using group by on store number, All Rows as aggregate and Adding Index starting from 1 then expanding sub tables again in columns. but still i see error. I have uploaded table as you said. Thanks

[ImkeF]

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"

[rajrajsha]

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"

---