Expand column where not all records are tables

[Anonymous]

Hi there,

i am trying to get all the records values few of the records are in table format, while showing that in front end how do i show all the underlying values in single column.

let
    Source = Xml.Tables(Web.Contents("https://www54.v1host.com/TCL/rest-1.v1/Data/Epic?sel=Number,Name,Custom_VendorName2&where=Category.N...")),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Attribute:total", Int64.Type}, {"Attribute:pageSize", type number}, {"Attribute:pageStart", Int64.Type}}),
    #"Expanded Asset" = Table.ExpandTableColumn(#"Changed Type", "Asset", {"Attribute", "Relation", "Attribute:href", "Attribute:id"}, {"Asset.Attribute", "Asset.Relation", "Asset.Attribute:href", "Asset.Attribute:id"}),
    #"Expanded Asset.Attribute" = Table.ExpandTableColumn(#"Expanded Asset", "Asset.Attribute", {"Element:Text", "Attribute:name", "Value"}, {"Asset.Attribute.Element:Text", "Asset.Attribute.Attribute:name", "Asset.Attribute.Value"}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Expanded Asset.Attribute", {"Asset.Attribute.Element:Text", "Asset.Attribute.Attribute:name", "Asset.Relation", "Asset.Attribute:href", "Asset.Attribute:id", "Attribute:total", "Attribute:pageSize", "Attribute:pageStart"}, "Attribute", "Value")
in
    #"Unpivoted Columns"

[ImkeF]

Not sure if I understand your request correct, but this could be what you're looking for: https://www.thebiccountant.com/2017/07/25/how-to-expand-a-column-that-cannot-be-expanded-in-power-bi...

[Anonymous]

i tried with the DAX reference you have provided, i column is showing error

Please refer the image i have added

[ImkeF]

Please paste the full code from the formula bar, as it now shows just the first row.

[Anonymous]

[ImkeF]

The code I've provided in my blogpost shouldn't be used in an added column-step, but reference the tables from the prevsious step as a whole like this:

Table.TransformColumns(#"Renamed Columns", {{“Column1”, each if Value.Is(_, type table) then _ else #table({“Column1”}, {{_}} ) }} )

But you can use the logic in an added column step if you reduce the code in the UI-window to this:

if Value.Is( [Column1], type table) then [Colum1] else #table({“Column1”}, {{_}} ) }}

[Anonymous]

IT show me to remove the brackets, after i have removed the bracket it shows error where there are tables. Please let me know is there a way i can share the pbix file with you so you can check the issue

[ImkeF]

Sorry, I missed a closing parenthesis:

if Value.Is( [Column1], type table) then [Colum1] else #table({“Column1”}, {{_}} ) }} )

& Don't convert that last "Column1" to [Column1] 😉

[Anonymous]

Still the dax function you have provided in not working, can you please help how to i write the query in power bi.

did not understood the meaning of

& Don't convert that last "Column1" to [Column1]

[ImkeF]

This is not a DAX-solution, but it uses the language M instead, as this is happening in the query editor instead of the data model.

 You've modified my code which causes it to break:

You can upload your file to a clould storage of your choice and paste the link here so that I can have a look at your file if it still doesn't work for you.

[Anonymous]

https://tatacommunications-my.sharepoint.com/:u:/p/rahul_jaiswar/EVJHLWgtdWhFvYC3aJMUwPYBw7AuHldAXTT...

Here is the pbix file in have uploaded, please take a look

[ImkeF]

Please paste public link that doesn't prompt me to authenticate.

[Anonymous]

Please try this out

https://drive.google.com/open?id=1YmV43msggX94uvLtE2bkm1a5zqNhPMil

[ImkeF]

Please try this file: https://1drv.ms/u/s!Av_aAl3fXRbehbJa7yGtVPdkZHYT8Q

I've changed the square brackets to parenthesis according to my instructions.

Couldn't test it though, as I have no access to your source data, so the queries will not show anything for me.

[Anonymous]

i am still getting the same issue, table values are still showing as error.

let me give you access to my data set. please use the following creditials so you can test the same.

Login : test.123

Pass : test

[ImkeF · Accepted solution]

Sorry, I had another typo in my formula, used [Colum1] instead of [Column1], but now it works 🙂

see file here: https://1drv.ms/u/s!Av_aAl3fXRbehbJa7yGtVPdkZHYT8Q

[Anonymous]

Thank you for you help, it worked now

Nice talking to you

[v-shex-msft]

HI @Anonymous,

Can you please provide some same data with expected result? I can't use your query to get data.

Regards,

XIaoxin Sheng

[Anonymous]

i am not able to uplodad the PBI file, can you share a path where i can upload the same.

[Greg_Deckler]

@ImkeF has skillz to deal with this sort of thing.