Replicating Excel Offset (With Dynamic range) in Power Query

Power Query Data Setup
1

Hi Team,

Recently i have been tasked to create a data model on top an excel file. Things got interesting as many column in excel files are using “Offset” function. All in all till the time range in offset function was static i was able to replicate it in Power Query. However i am unable to replicate dynamic offset in power query. Has anybody done anything like this before? I am uploading a sample excel file.I am trying to built the last 2 columns that are marked as green.
All offset is doing is that it is trying to offset a range of columns based on a value in a cell in excel. In my file in row 2 offset function is building a range starting from column B and designating it as 0 index and then counting the number of columns to offset based on the value written in column BB for a specific row. Once it offset this range than it again creates a range starting from next column and goes uptill the value in column BC . It then takes this range and check if the values in this range is less than 2 if yes than it return its count else a text label.
I know it a lot to understand from my written explanation. I am more than happy in answering the question related to the data.

Sample Data.xlsx (97.0 KB)

@Melissa @BrianJ

Regards

2

Hi @Hemantsingh,

I’ve taken a small sample from your data, broke your logic up into individual steps for easy validation and extracted the results. Just copy this full script into a new blank query.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("1VRbbsMwDLtLvj3AlOXXWYre/xpLSLlIgWXYx4Z2H6ICS5ZlhtbttvXsVkcpMBTf0tZS2bESnViIRgQxR3QyOhmdjE5GiWGewCwwC8wCs3DKeqWNMMtWzOIDdbun29Zyy4udRl6OrkdyYt2xExsRvN5Rk2wY0wC5LCc+IEIQjIgS5BXzq+0zTnaerP/l7MvJtZNrJ9fcRtTev+SvLtrmM39ndbUHTyN19t+50tl/Z/+d/Xfe1JhpzDRmGjONmcZMi/NZKfogB0cbx/3/ga4m7zZ45/qlrmaoDlwH1/FQ4/mFXa2/78v7iXIYqunsNHwspo/Gj2n+mAYQptyQ63JNTlWgKlAVxBDDk1Mx1VIpVVIh1fnZJHyRzrCz6LuNxfbVOwLhaYQNyrIHfvdcjQhiFr6BHSPBFyNtfZSgZow6bFruqL+unveXzZUdKvhYMsqLtDy3+/0T", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [DCItem = _t, #"202307" = _t, #"202308" = _t, #"202309" = _t, #"202310" = _t, #"202311" = _t, #"202312" = _t, #"202313" = _t, #"202314" = _t, #"202315" = _t, #"202316" = _t, #"202317" = _t, #"202318" = _t, #"202319" = _t, #"202320" = _t, #"202321" = _t, #"202322" = _t, #"202323" = _t, #"202324" = _t, #"202325" = _t, #"202326" = _t, #"202327" = _t, #"202328" = _t, #"202329" = _t, #"202330" = _t, #"202331" = _t, #"202332" = _t, #"202333" = _t, #"202334" = _t, #"202335" = _t, #"202336" = _t, #"202337" = _t, #"202338" = _t, #"202339" = _t, #"202340" = _t, #"202341" = _t, #"202342" = _t, #"202343" = _t, #"202344" = _t, #"202345" = _t, #"202346" = _t, #"202347" = _t, #"202348" = _t, #"202349" = _t, #"202350" = _t, #"202351" = _t, #"202352" = _t, #"202401" = _t, #"202402" = _t, #"202403" = _t, #"202404" = _t, #"202405" = _t, #"202406" = _t, #"Current Week to Shipment Receipt Week + 2" = _t, #"Actionable Range" = _t, #"Order Receipt Wk -1" = _t, #"TW Shipment Receipt + 2" = _t]),
    ChType = Table.TransformColumnTypes(Source,{{"DCItem", type text}, {"Current Week to Shipment Receipt Week + 2", Int64.Type}, {"Actionable Range", Int64.Type}, {"Order Receipt Wk -1", Int64.Type}, {"TW Shipment Receipt + 2", Int64.Type}, {"202307", type number}, {"202308", type number}, {"202309", type number}, {"202310", type number}, {"202311", type number}, {"202312", type number}, {"202313", type number}, {"202314", type number}, {"202315", type number}, {"202316", type number}, {"202317", type number}, {"202318", type number}, {"202319", type number}, {"202320", type number}, {"202321", type number}, {"202322", type number}, {"202323", type number}, {"202324", type number}, {"202325", type number}, {"202326", type number}, {"202327", type number}, {"202328", type number}, {"202329", type number}, {"202330", type number}, {"202331", type number}, {"202332", type number}, {"202333", type number}, {"202334", type number}, {"202335", type number}, {"202336", type number}, {"202337", Int64.Type}, {"202338", Int64.Type}, {"202339", Int64.Type}, {"202340", Int64.Type}, {"202341", Int64.Type}, {"202342", Int64.Type}, {"202343", Int64.Type}, {"202344", Int64.Type}, {"202345", Int64.Type}, {"202346", Int64.Type}, {"202347", Int64.Type}, {"202348", Int64.Type}, {"202349", Int64.Type}, {"202350", Int64.Type}, {"202351", Int64.Type}, {"202352", Int64.Type}, {"202401", Int64.Type}, {"202402", Int64.Type}, {"202403", Int64.Type}, {"202404", Int64.Type}, {"202405", Int64.Type}, {"202406", Int64.Type}}),
    AddRecord = Table.AddColumn(ChType, "temp", each 
        [
            c = List.Buffer( List.LastN( Record.ToList( _ ), 4 )),
            v = List.RemoveLastN( List.Skip( Record.ToList( _ ), 1 ), 4),
            s = c{3} <= c{2},
            r = List.Buffer( List.FirstN( List.Skip( v, c{0} ), c{1})),
            #"Low Inv - Actionable Period" = if s then List.Count( List.Select( r, each _ <2 )) else "Item Setting Error",
            #"High Inv - Actionable Period" = if s then List.Count( List.Select( r, each _ <10 )) else "Item Setting Error"
        ]
    ),
    ExpandTemp = Table.ExpandRecordColumn(AddRecord, "temp", {"Low Inv - Actionable Period", "High Inv - Actionable Period"}, {"Low Inv - Actionable Period", "High Inv - Actionable Period"})
in
    ExpandTemp

I hope this is helpful.

2 Likes
3

Hello @Hemantsingh

Did the responses above help solve your query?

If not, can you let us know where you’re stuck and what additional assistance you need?

If it did, please mark the answer as the SOLUTION by clicking the three dots beside Reply and then tick the check box beside SOLUTION

Thank you

4

Hi @Melissa ,

Thanks for the solution. I am still reviewing it. Also, the second step in your solution hard codes the column data type. It is good for the column that are text labeled but not good for weekyear column names as they are like “202302”. Once we hardcode the datatype for these weekyear name columns then next week when the new file comes in with new weekyear column than this solution wont work.

Any idea to avoid hardcoding the data type?

Regards,
Hemant

5

Hi @Hemantsingh

Due to inactivity, a response on this post has been tagged as “Solution”.

If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box.

We request you to kindly take time to answer the Enterprise DNA Forum User Experience Survey,.

We hope you’ll give your insights on how we can further improve the Support forum. Thanks!