Forum Discussion

Alexck's avatar
Alexck
Regular Visitor
4 years ago
Solved

Table expansion not working

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!

Need Help
  • wdx223_Daniel's avatar
    wdx223_Daniel
    4 years ago

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

9 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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's avatar
      Alexck
      Regular Visitor
      4 years ago

      Hi Anonymous ,

      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,

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Hi Alexck 

         

        it should be the original column name

         Table.AddColumn(yourPreStep, "newColumnName", each Record.ToList( [CpfCnpj]{0}){0})
  • Alexck's avatar
    Alexck
    Regular Visitor
    4 years ago

    Hi Anonymous ,

     

    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

  • wdx223_Daniel's avatar
    wdx223_Daniel
    Community Champion
    4 years ago

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

    • Alexck's avatar
      Alexck
      Regular Visitor
      4 years ago

      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's avatar
        wdx223_Daniel
        Community Champion
        4 years ago

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

  • mussaenda's avatar
    mussaenda
    Community Champion
    4 years ago

    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's avatar
      mussaenda
      Community Champion
      4 years ago

      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"})