San Antonio Power BI User Group

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
Expand all | Collapse all

Dashed and Comma Separated Lists

  • 1.  Dashed and Comma Separated Lists

    Posted Apr 27, 2020 07:12 PM
    Anyone have any idea how to do the reverse of this link.
    Chris Webb's BI Blog: Converting Lists Of Numbers To Text Ranges In Power Query

    Example 2290-2297,2299,2304,2310-2313
    Needs to be
    2290
    2291
    2292
    etc...
    2297
    2299
    2304
    2310
    2311
    2312
    etc...

    ------------------------------
    Jason Haagen
    IT/CAD Services Manager
    San Antonio TX
    210-681-2951
    ------------------------------


  • 2.  RE: Dashed and Comma Separated Lists

    Gold Contributor
    Posted Apr 27, 2020 09:24 PM
      |   view attached
    Something like this?

    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjKyNNAFEuY6QMJSx8jYwARIGALFjA2NlWJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Item = _t]),
        #"Changed Type1" = Table.TransformColumnTypes(Source,{{"Item", type text}}),
        #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type1", "Item", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Item.1", "Item.2", "Item.3", "Item.4"}),
        #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Item.1", type text}, {"Item.2", Int64.Type}, {"Item.3", Int64.Type}, {"Item.4", type text}}),
        #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type2", {}, "Attribute", "Value"),
        #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}),
        #"Split Column by Delimiter1" = Table.SplitColumn(Table.TransformColumnTypes(#"Removed Columns", {{"Value", type text}}, "en-US"), "Value", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"Value.1", "Value.2"}),
        #"Renamed Columns" = Table.RenameColumns(#"Split Column by Delimiter1",{{"Value.1", "Start Number"}, {"Value.2", "End Number"}}),
        #"Replaced Value" = Table.ReplaceValue(#"Renamed Columns",null,"0",Replacer.ReplaceValue,{"End Number"}),
        #"Changed Type3" = Table.TransformColumnTypes(#"Replaced Value",{{"Start Number", Int64.Type}, {"End Number", Int64.Type}}),
        #"Added Custom" = Table.AddColumn(#"Changed Type3", "Custom", each Text.Combine(List.Transform({[Start Number]..[End Number]},Text.From),":")),
        #"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"End Number"}),
        #"Split Column by Delimiter2" = Table.SplitColumn(#"Removed Columns1", "Custom", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Custom.1", "Custom.2", "Custom.3", "Custom.4", "Custom.5", "Custom.6", "Custom.7", "Custom.8"}),
        #"Changed Type4" = Table.TransformColumnTypes(#"Split Column by Delimiter2",{{"Custom.1", Int64.Type}, {"Custom.2", Int64.Type}, {"Custom.3", Int64.Type}, {"Custom.4", Int64.Type}, {"Custom.5", Int64.Type}, {"Custom.6", Int64.Type}, {"Custom.7", Int64.Type}, {"Custom.8", Int64.Type}}),
        #"Unpivoted Columns1" = Table.UnpivotOtherColumns(#"Changed Type4", {}, "Attribute", "Value"),
        #"Removed Duplicates" = Table.Distinct(#"Unpivoted Columns1", {"Value"}),
        #"Removed Columns2" = Table.RemoveColumns(#"Removed Duplicates",{"Attribute"})
    in
        #"Removed Columns2"


    ------------------------------
    Lawrence Coffee Director, Business and Technology at Vaco - a Solutions and Talent Firm
    lawrence.coffee@vaco.com
    ------------------------------

    Attachment(s)

    pbix
    Test.pbix   41K 1 version


  • 3.  RE: Dashed and Comma Separated Lists

    Posted Apr 28, 2020 08:59 AM

    Perfect. Thanks!
    This is the step I was missing:

    = Table.AddColumn(#"Changed Type3", "Custom", each Text.Combine(List.Transform({[Start Number]..[End Number]},Text.From),":"))

    List.Transform


    ------------------------------
    Jason Haagen
    IT/CAD Services Manager
    San Antonio TX
    210-681-2951
    ------------------------------



  • 4.  RE: Dashed and Comma Separated Lists

    Gold Contributor
    Posted Apr 28, 2020 11:24 AM
    Glad to help! If you don't mind - this would be a great addition to a tips and tricks section we're adding this week to the Agenda. Would love to add content for the advanced users. Not sure where it will go yet, but I started with the add row in Power Query Feature. Thinking its quick tips and tricks - ideally only a minute or two per feature.

    ------------------------------
    Lawrence Coffee Director, Business and Technology at Vaco - a Solutions and Talent Firm
    lawrence.coffee@vaco.com
    ------------------------------