Forum Discussion
Generate row for each value between a starting and ending value (Image and Pbix file included)
Hello all!
Table A is an unchanging and pre-sorted table containing every combination of Facility and Location Code.
Table B is where users will enter the starting and ending locations as well as the facility
Table C is my desired result, a list of all location codes between the starting and ending locations entered by the user, along with the facility code. Note that the same location code may be seen in multiple facilities.
Pbix file: https://files.catbox.moe/abw3o5.pbix
Thank you!
This is the code for User Entered Data (Your pbix file is attached)
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtY1MNE1MFTSUQoGEc4Ghs5gyshJKVYHpsJU18gApMIIIuWIqcJM18AISYUTXEUsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Facility = _t, Starting_Location = _t, Ending_Location = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Facility", type text}, {"Starting_Location", type text}, {"Ending_Location", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each [a=List.Zip({All_Locations[Facility], All_Locations[Location_Code]}), p1=List.PositionOf(a, {[Facility],[Starting_Location]}), p2=List.PositionOf(a, {[Facility],[Ending_Location]}), b=List.Range(All_Locations[Location_Code], p1, p2-p1+1)][b])[[Date], [Facility], [Custom]], #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom") in #"Expanded Custom"
2 Replies
This is the code for User Entered Data (Your pbix file is attached)
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtY1MNE1MFTSUQoGEc4Ghs5gyshJKVYHpsJU18gApMIIIuWIqcJM18AISYUTXEUsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Facility = _t, Starting_Location = _t, Ending_Location = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Facility", type text}, {"Starting_Location", type text}, {"Ending_Location", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each [a=List.Zip({All_Locations[Facility], All_Locations[Location_Code]}), p1=List.PositionOf(a, {[Facility],[Starting_Location]}), p2=List.PositionOf(a, {[Facility],[Ending_Location]}), b=List.Range(All_Locations[Location_Code], p1, p2-p1+1)][b])[[Date], [Facility], [Custom]], #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom") in #"Expanded Custom"THANK YOU!!!!!!!!!!!!!!!!!!!!
The .Pbix file looks good, I'll implement it into my actual project and then accept as solution if it works!
