Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
Hi Everyone,
I have a data as below. I would like to split my table into 3 small table at these rows which is null of column "item".
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
add_index = Table.AddIndexColumn(Source,"index",1,1),
list_of_position = Table.SelectRows(add_index,(r)=>r[item]=null)[index],
Custom1 = List.Transform(list_of_position,(item)=>
Table.SplitAt(add_index,item)
)
in
Custom1
I tried with Table.SplitAt , however this function can not work with list of number. Could you please assist for this case?
Solved! Go to Solution.
See the sample code below
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TY47DgQhDEPvknoayIfZsyDKXIBB4vobk5V2Ckf44VjpnegiFY3JxjSuTpXDlBrjeQ5Qw29ozn1APOGWZ15KOAHZex5k2EDnBsolEUBL+CMobUki+Oq29jLtzjNQeId8+cqwMho+oeX+LzhJ5VqUxvgC", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Item = _t, Category = _t, Test = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Item", Int64.Type}, {"Category", Int64.Type}}),
cnt = Table.RowCount(#"Changed Type"),
list_item = List.Buffer(#"Changed Type"[Item]),
temp_tbl = Table.FromColumns(Table.ToColumns(#"Changed Type") & {List.Generate(()=>[x=0,i=0], each [i]<cnt, each [i=[i]+1, x=if list_item{i}=null and list_item{[i]}<>null and i+1<>cnt then [x]+1 else [x]], each [x])}, Table.ColumnNames(#"Changed Type") & {"Index"}),
#"Grouped Rows" = List.Transform(Table.Group(temp_tbl, {"Index"}, {{"All", each _}})[All], (x)=>Table.RemoveColumns(Table.SelectRows(x, each _[Item]<>null), "Index"))
in
#"Grouped Rows"
Hi @thangdev,
2 versions of code here (v2 is faster and similar to Vijay's)
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("tU5bDsMgDLtLvvvTUmj3ufchUP+WHQCQcv3ZYUK7wCQcxSaxk7OcZZK4RtSQghxTlhv6eUGp1fmVf0Ap5hwtWVNnF/QruVlx4U5DwKj0DWakLg3HrXMM/ZimbVzAkX0Y7oA2bTYyT0BT/a4/GMrUGJY5uvTkEFPr6433j5TjAw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Item = _t, Category = _t, Test = _t]),
v1_ReplacedValue = Table.ReplaceValue(Source,"",null,Replacer.ReplaceValue,{"Item", "Category"}),
v1_ItemNullPositions = [ nullPos = Table.FromList(List.Transform(List.PositionOf(v1_ReplacedValue[Item], null, Occurrence.All), each _ +1), Splitter.SplitByNothing(), {"Pos from"}),
nullPos2 = #table({"Pos from"}, {{0}}) & nullPos,
adIndex = Table.AddIndexColumn(nullPos2, "Index", 0, 1, Int64.Type),
count = Table.AddColumn(adIndex, "Count", each try adIndex{[Index]}[Pos from] - adIndex{[Index] -1}[Pos from] otherwise adIndex{[Index] +1}[Pos from] -1)
][count],
v1_SeparateTables = List.Accumulate(
Table.ToRecords(v1_ItemNullPositions),
{},
(s,c)=> s & { Table.Range(v1_ReplacedValue, c[Pos from], c[Count]) }
),
v2_StepBack = Source,
v2_ReplacedValue = Table.ReplaceValue(v2_StepBack,"",null,Replacer.ReplaceValue,{"Item", "Category"}),
v2_Ad_GroupId = [
l_item = List.Buffer(v2_ReplacedValue[Item]),
lg = List.Generate(
()=> [ x = 0, y = if l_item{0} = null then null else 1 ],
each [x] < List.Count(l_item),
each [ x = [x]+1, y = if l_item{x} = null then [y]+1 else [y] ],
each [y]
),
combined = Table.SelectRows(Table.FromColumns(Table.ToColumns(v2_ReplacedValue) & {lg}, Table.ColumnNames(v2_ReplacedValue) & {"Group Id"}), each [Item] <> null)
][combined],
#"v2_Grouped Rows" = Table.Group(v2_Ad_GroupId, {"Group Id"}, {{"All", each _, type table}}),
v2_All = #"v2_Grouped Rows"[All]
in
v2_All
See the sample code below
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TY47DgQhDEPvknoayIfZsyDKXIBB4vobk5V2Ckf44VjpnegiFY3JxjSuTpXDlBrjeQ5Qw29ozn1APOGWZ15KOAHZex5k2EDnBsolEUBL+CMobUki+Oq29jLtzjNQeId8+cqwMho+oeX+LzhJ5VqUxvgC", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Item = _t, Category = _t, Test = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Item", Int64.Type}, {"Category", Int64.Type}}),
cnt = Table.RowCount(#"Changed Type"),
list_item = List.Buffer(#"Changed Type"[Item]),
temp_tbl = Table.FromColumns(Table.ToColumns(#"Changed Type") & {List.Generate(()=>[x=0,i=0], each [i]<cnt, each [i=[i]+1, x=if list_item{i}=null and list_item{[i]}<>null and i+1<>cnt then [x]+1 else [x]], each [x])}, Table.ColumnNames(#"Changed Type") & {"Index"}),
#"Grouped Rows" = List.Transform(Table.Group(temp_tbl, {"Index"}, {{"All", each _}})[All], (x)=>Table.RemoveColumns(Table.SelectRows(x, each _[Item]<>null), "Index"))
in
#"Grouped Rows"
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
15 | |
11 | |
8 | |
8 | |
7 |
User | Count |
---|---|
15 | |
13 | |
9 | |
7 | |
6 |