Flatten Table with PowerQuery for Ranges

[luxtra91]

Hi Community,

I'm hoping for some Ideas on my Problem.

I have a Table containing Labels, Line Numbers, and Information a a range of Labels.
I need to flatten this Table. In the Image below, I attached a Sample of the Data Layout and the desired output.

The desired Output could be in the form of a List or a Table, so I can expand it.
I can deal with the numeric ranges without problems, after converting them to Numbers:

= Table.AddColumn(#"Changed Type1", "CustomList", each if [From] = null then null else List.Numbers([From], [To]-[From]+1))

I do have trouble with the Labels. 

I tried something like:

= Table.AddColumn(#"Source", "Custom", each Table.SelectRows(#"Source",each [Label] >= _[From] and [Label] <= _[To] ))

But I cant work out how to get the Field access from inside the function right. This will also not work, if the Labels are not in strinct alphabetical order (which they are not - but I could merge the label with the corresponding Line Number).

Any Ideas? 

Is this reallz that complicated, or is my brain just fried after a hard day?

Thank in advance!

[Anonymous · Accepted solution]

Hi @luxtra91 ,

Please try the following steps:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wck4sUTBU0lECoVidaKWAeJCIEZBrYGhoAKFMwDJglXrIaiEiRhgiphgiZmgixkAOwjyYrthYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Label = _t, From = _t, To = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Label", type text}, {"From", type text}, {"To", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each if [From]="" or [To] = "" then {}
else if Text.Contains([From],"Cat") then 
    let
    FromIndex = Table.SelectRows(#"Added Index", (x)=>x[Label]=[From])[Index]{0},
    ToIndex = Table.SelectRows(#"Added Index", (x)=>x[Label]=[To])[Index]{0},
    Result = Table.SelectRows(#"Added Index", each List.Contains({FromIndex..ToIndex}, [Index]))[Label]
in
    Result
else if Logical.From(Number.From([From])) then
    List.Transform({Number.From([From]).. Number.From([To])}, each "0" & Text.From(_))
else null)
in
    #"Added Custom"

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group

[luxtra91]

Thanks! 

The Function in the Select Rows Statement solved all my field access issues and I could easily adapt my code with it.

Table.SelectRows(#"Added Index", (x)=>x[Label]=[From])[Index]{0}