Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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!
Solved! Go to 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"
@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"
User | Count |
---|---|
22 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
25 | |
13 | |
11 | |
9 | |
6 |