Search code examples
powerquerym

Create unique records from intervals powerquery

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

Solution

  • 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