count how many tables exists in each column, and expand this column if there or 2 or more.

[Peter1975]

I want to count how many tables exists in each column. If there two or more than then i want to Expand this table.

In this example, column2 and column3 have to expand.

Who can help me with this? 

column1	column2	column3
abc	table	table
def	table	pqr
table	jkl	table
ghi	mno	stu

Greetz Peter

[ImkeF]

Hello @Peter1975 ,
you can try the following method:

let
    Source = #table({"Column1", "Column2"}, {{#table({}, {{}}), 1}}), 
    GetColumnNames = Table.FromList(Table.ColumnNames(Source)), 
    AddTableCount = Table.AddColumn(
        GetColumnNames, 
        "CountOfTableObjects", 
        each List.Count(List.Select(Table.Column(Source, [Column1]), (x) => Value.Is(x, Table.Type)))
    )
in
    AddTableCount

paste the code into the advanced editor and follow the steps.
It will return a table with one row for each column, giving the count of table objects.

[Peter1975]

Thank you Imke for your answer.

Although this offers part of the solution, it does not quite give the solution to my problem.

I would like to degroup (expand) each column as soon as a column contains 2 or more tables.

I added some code, but i don't know how to fix the code in the loop-part.

Can you help me?

let
    Source = #table({"Column1", "Column2"}, {{#table({}, {{}}), 1}}), 
    GetColumnNames = Table.FromList(Table.ColumnNames(Source)), 
    NewColumnNames = List.Transform(GetColumNames,  each _ & "_"),
    AddTableCount = Table.AddColumn(
        GetColumnNames, 
        "CountOfTableObjects", 
        each List.Count(List.Select(Table.Column(Source, [Column1]), (x) => Value.Is(x, Table.Type)))
    )

  //here a loop for each column(if i have 10 columns for example)
  if [CountOfTableObjects]>1 
  then Table.ExpandTableColumn(Source, Column{1}, GetColumnNames, NewColumnNames)
  else
  next column
  //end of loop

in
TableCount