Create unique records from intervals powerquery

0
votes

I have a 3 column excel table where I want to store a group and 2 dimentions. Dim 1 is always just a single value Dim 2 can be multiple intervals separated by commas or just a single value.

My aim is to generate a table with all different combinations in powerquery in order to use it as a key. Some help on this would be much appreciated!

Start:

enter image description here

Finish:

enter image description here

2
powerquerym

2 Answers

1
votes

I ended up using this but i'm very grateful for your code :)

let
    Källa = Excel.CurrentWorkbook(){[Name="Tabell1"]}[Content],
    #"Ändrad typ" = Table.TransformColumnTypes(Källa,{{"Group", type text}, {"Dim1", type text}, {"Dim2", type any}}),
    #"Ändrad typ1" = Table.TransformColumnTypes(#"Ändrad typ",{{"Dim2", type text}}),
    #"Lägg till egen" = Table.AddColumn(#"Ändrad typ1", "Ansvar", each let  
    commaList = Text.Split([Dim2], ","),
    
    totList = List.Accumulate(commaList, {}, (state, current) =>
      let
        intervalList =  Text.Split(current,"-"),
        startValue = Number.From(List.First(intervalList)),
        endValue = Number.From(List.Last(intervalList)),
        genList = state  & List.Generate(() => startValue, each _ <= endValue, each _ + 1)
      in
      genList
    ),
    transformedList = List.Transform(totList, each Number.ToText(_)),
    stringFromList = Text.Combine(transformedList, ", ")
in  
    transformedList),
    #"Borttagna kolumner" = Table.RemoveColumns(#"Lägg till egen",{"Dim2"}),
    #"Expanderad Ansvar" = Table.ExpandListColumn(#"Borttagna kolumner", "Ansvar")
in
    #"Expanderad Ansvar"
0
votes

I used the following M-Code to transform your start table into the desired result

let
    Quelle = Excel.CurrentWorkbook(){[Name="tblData"]}[Content],
    #"Geänderter Typ" = Table.TransformColumnTypes(Quelle,{{"group", type text}, {"dim1", Int64.Type}, {"dim2", type text}}),
    #"Hinzugefügte benutzerdefinierte Spalte" = Table.AddColumn(#"Geänderter Typ", "dim2_list", each Text.Split([dim2],",")),
    #"Erweiterte Benutzerdefiniert" = Table.ExpandListColumn(#"Hinzugefügte benutzerdefinierte Spalte", "dim2_list"),
    #"Entfernte Spalten" = Table.RemoveColumns(#"Erweiterte Benutzerdefiniert",{"dim2"}),
    #"Hinzugefügte benutzerdefinierte Spalte1" = Table.AddColumn(#"Entfernte Spalten", "dim2", each fnCreateList([dim2_list])),
    #"Erweiterte dim2" = Table.ExpandListColumn(#"Hinzugefügte benutzerdefinierte Spalte1", "dim2"),
    #"Entfernte Spalten1" = Table.RemoveColumns(#"Erweiterte dim2",{"dim2_list"})
in
    #"Entfernte Spalten1"

Function fnCreateList used in the above code

(inp as text)  =>
let
    secondNo= Number.FromText(Text.End(inp,Text.Length(inp)- Text.PositionOf(inp,"-")-1)),
    firstNo =try Number.FromText(Text.Start(inp,Text.PositionOf(inp,"-"))) otherwise secondNo,
    result= {firstNo .. secondNo}       
in
    result