Split row into multiple rows depending on the number of weeks

[Anonymous]

Hello

I am a rookie with Power BI. Could you help me with a number of rows problem?

How can I split a delimiter-separated-week-series row into multiple rows depending on the number of weeks? For instance in the example below from the row "31-34" there would become four rows (the green ones) with single week number on each row.

I would also like to put the new rows to a new table. How can I do this?

Thanks!

 

[Anonymous]

@Greg_DecklerPerfect, thank you!

[Greg_Deckler · Accepted solution]

@Anonymous You could try something like this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCk9NzS42VNJRMjbUNTZRio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column", type text}, {"Value", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Value", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"Value.1", "Value.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Value.1", Int64.Type}, {"Value.2", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom", each List.Numbers( [Value.1], [Value.2] - [Value.1] + 1)),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Value.1", "Value.2"})
in
    #"Removed Columns"