Forum Discussion
Solved
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 wel...
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"
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!
