Table expansion not working

[Alexck]

Hi all,

I am opening a XML file and after expanding the tables I get stuck with a not expandable table, but it has data inside which should be converted into collumns.

As you can see, the data may vary from rwo to row, but I wanted these rows to be shown with the data below.

How would I do that?

Thanks in advance!

[mussaenda]

Hi @Alexck ,

Can try this solution on a new custom column?

if [the column with table] = "" 
then ""
else 

Table.Column(the column with table, "Cpf")

Please let me know if it worked. If yes, you can do also for the other one which is Cnpj.

[mussaenda]

Another one I am thinking is using the Table.SelectColumns.

Please try the  suggestions below if it will work:

if [the column with table] = "" 
then ""
else 

Table.SelectColumns(the column with table, {"Cpf", "Cnpj"})

[wdx223_Daniel]

NewStep=Table.ExpandTableColumn(Table.TransformColumns(PreviousStep,{"CpfCnpj",each if _ is table then _ else #table({"Cpf"},{})}),"CpfCnpj",{"Cpf"})

[Alexck]

Hi @wdx223_Daniel ,

Thank you very much for your input.

It actually worked, but it only shows the CPF. The Cnpj and blanks are hidden.

I've duplicated the function, but it overwrites the previous one. Can it be done at once?

Thanks,

[wdx223_Daniel · Accepted solution]

NewStep=Table.ExpandTableColumn(Table.TransformColumns(PreviousStep,{"CpfCnpj",each if _ is table then _ else #table({"Cpf"},{})}),"CpfCnpj",{"Cpf","Cnpj"})

[Alexck]

Hi @Vera_33 ,

Thank you very much for the explanation! 

Unfortunately I'm not very familiar with these codes, therefore I couldn't really understand how could I fill the data in the line you sent to me.

Could you please be so kind and explain what each comand do?

Thanks

[Vera_33]

Hi @Alexck 

You can add a custom column to get the value, if you have only one column and one value like the examples above, and wrap try...otherwise if any error occurs

 Table.AddColumn(yourPreStep, "newColumnName", each Record.ToList( [yourOriginColumn]{0}){0})

[Alexck]

Hi @Vera_33 ,

I've inputed the format you provided me, but it didn't work correctly. Problably I'm messing something up.

Is this what you meant?

Thanks,

[Vera_33]

Hi @Alexck 

it should be the original column name

 Table.AddColumn(yourPreStep, "newColumnName", each Record.ToList( [CpfCnpj]{0}){0})