Solved: Re: Expand column where not all records are tables - Page 2

nhoff · ‎10-17-2016

From an original XML-file I am trying to expand this column:

When I do this, I get the following result:

Some records are not at the 'text'-level, some are still tables. If I mark one of the records with table value, I can see the following content:

If I now try to expand the column, I get the following error:

How do I expand the column?

ImkeF · ‎10-18-2016

This seems to be a bit tricky. You add a column that checks if the content of the current row of the column to expand is of type table. In that case you take the value as it is and if not, you transform the text-value to table. That way all fields will be in table format and can be expanded:

let

    Source = {1, #table({"a", "A"}, {{"b", "B"}})},
    #"In Tabelle konvertiert" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    Step2 = Table.AddColumn(#"In Tabelle konvertiert", "Custom", each if Value.Is([Column1], type table) then [Column1] else #table({"ConvertedText"}, {{[Column1]}})),
    #"Erweiterte Custom" = Table.ExpandTableColumn(Step2, "Custom", Table.ColumnNames(Table.Combine(Step2[Custom])), Table.ColumnNames(Table.Combine(Step2[Custom])))
in
    #"Erweiterte Custom"

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

Anonymous · ‎05-12-2017

Hi ImkeF.

I have inserted your code into a new query. Do I just need to change the source to my source?

I'm unsure as to amend your code so that it works on my source data.

Sorry for the dumb questions, I am new to Power BI!

Regards

Mike

Anonymous · ‎05-16-2017

Hi ImkeF,

I have managed to get this to work using your code.

Thank you so much for your help! 🙂

Regards

Mike

Anonymous · ‎05-22-2017

Hi ImkeF,

This line of code is returning a'null' when the value in Column1 is type table.

Step2 = Table.AddColumn(#"In Tabelle konvertiert", "Custom", each if Value.Is([Column1], type table) then [Column1] else #table({"ConvertedText"}, {{[Column1]}})),

Any idea why?

Thanks

Mike

ImkeF · ‎05-22-2017

Does is directly return a null or after you've expanded the column that this steps creates?

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Anonymous · ‎05-22-2017

Hi Imke F,

It was after the expansion of the columns. I have figured it out now. It was due to the column heading being a new heading, and the expansion wanted to expand two columns, but it only expanded one.

Thanks

Mike

Expand column where not all records are tables

Helpful resources

Join us at the Microsoft Fabric Community Conference

Power BI Monthly Update - February 2025

Fabric Community Update - February 2025

How to Get Your Question Answered Quickly

Join us at the 2025 Microsoft Fabric Community Conference

Expand column where not all records are tables

Helpful resources

Join us at the Microsoft Fabric Community Conference

Power BI Monthly Update - February 2025

Fabric Community Update - February 2025

How to Get Your Question Answered Quickly