Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to Solution.
NewStep=Table.ExpandTableColumn(Table.TransformColumns(PreviousStep,{"CpfCnpj",each if _ is table then _ else #table({"Cpf"},{})}),"CpfCnpj",{"Cpf","Cnpj"})
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.
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"})
NewStep=Table.ExpandTableColumn(Table.TransformColumns(PreviousStep,{"CpfCnpj",each if _ is table then _ else #table({"Cpf"},{})}),"CpfCnpj",{"Cpf"})
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,
NewStep=Table.ExpandTableColumn(Table.TransformColumns(PreviousStep,{"CpfCnpj",each if _ is table then _ else #table({"Cpf"},{})}),"CpfCnpj",{"Cpf","Cnpj"})
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
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})
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,
Hi @Alexck
it should be the original column name
Table.AddColumn(yourPreStep, "newColumnName", each Record.ToList( [CpfCnpj]{0}){0})
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
102 | |
48 | |
19 | |
13 | |
11 |