Solved: How to open value "Table" in column in PowerQuery

D3K · ‎02-09-2020

Hey everyone!

I suppose it's a very simple case, but I really have no idea, how to solve it.

I've got a column in PowerQuery, which contains value/link "Table". If I click and open it - there is only 1 value.

How to "open" this column?

Please, check the picture below.

Thanks a lot for your help!

smpa01 · ‎02-09-2020

@D3K in your case you can't expand those tables because not all the rows in that column has table value. Unless all the records are table in that column you can't apply Table.ExpandTableColumn.

But there is a workarounnd. Let's suppose your source data is following

Template	Description1	Description2
Q1	Raushan	abc
Q1	Raushan	def
Q1	Raushan	ghi
Q1	Raushan	jkl
Q2	Sanjay	AB
Q2	Sanjay	CD
Q2	Sanjay	EF

And you find yourself in this step

You can do this

/// you need to customize from here on to fit your scenario 
 #"Added Custom1" = Table.AddColumn(Custom1, "Custom", each Value.Type([Column1])),
    Custom2 = Table.AddColumn(#"Added Custom1", "Custom.1", each Type.Is([Custom],Table.Type)),
    #"Added Custom3" = Table.AddColumn(Custom2, "Custom.2", each if [Custom.1] =false then Table.RemoveColumns(Table.Group(#table({"Description1", "Description2"}, {{"",""}}), {"Description1"}, {{"ad", each _, type table [Description1=text, Description2=text]}}),{"Description1"}) else [Column1]),
    Custom4 = Table.AddColumn(Custom2, "Custom.2", each if [Custom.1] =false then #table({"Description1", "Description2"}, {{"",""}}) else [Column1]),
    #"Added Custom2" = Table.AddColumn(Custom4, "Custom.3", each if [Custom.1] = false then  [Column1] else " "),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom2",{"Custom", "Custom.1"}),
    #"Expanded Custom.2" = Table.ExpandTableColumn(#"Removed Columns1", "Custom.2", {"Description1", "Description2"}, {"Description1", "Description2"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded Custom.2",{"Column1", "Custom.3", "Description1", "Description2"}),
    #"Removed Columns2" = Table.RemoveColumns(#"Reordered Columns",{"Column1"})
in
    #"Removed Columns2"

to come to this

the full code is here

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCknNLchJLElV0lFySS1OLsosKMnMzzNE5RopxepEKwWCRIMSS4szEvOArMSkZGzCKalp2ITTMzKxCWdl50CEjYCc4MS8rMRKIMPRCYugswsWQVc3pdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Template", type text}, {"Description1", type text}, {"Description2", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type1", {"Template"}, {{"ad", each _, type table [Template=text, Description1=text, Description2=text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each let
X = [ad],
Y = Table.RemoveColumns(X,{"Template"})
in Y),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"ad"}),
    #"Transposed Table" = Table.Transpose(#"Removed Columns"),
    X = Table.RemoveColumns(#"Transposed Table",{"Column2"}),
    Y = Table.RemoveColumns(#"Transposed Table",{"Column1"}),
    Z = Table.RenameColumns(Y,{{"Column2", "Column1"}}),
    Custom1 = X&Z,
    #"Added Custom1" = Table.AddColumn(Custom1, "Custom", each Value.Type([Column1])),
    Custom2 = Table.AddColumn(#"Added Custom1", "Custom.1", each Type.Is([Custom],Table.Type)),
    #"Added Custom3" = Table.AddColumn(Custom2, "Custom.2", each if [Custom.1] =false then Table.RemoveColumns(Table.Group(#table({"Description1", "Description2"}, {{"",""}}), {"Description1"}, {{"ad", each _, type table [Description1=text, Description2=text]}}),{"Description1"}) else [Column1]),
    Custom4 = Table.AddColumn(Custom2, "Custom.2", each if [Custom.1] =false then #table({"Description1", "Description2"}, {{"",""}}) else [Column1]),
    #"Added Custom2" = Table.AddColumn(Custom4, "Custom.3", each if [Custom.1] = false then  [Column1] else " "),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom2",{"Custom", "Custom.1"}),
    #"Expanded Custom.2" = Table.ExpandTableColumn(#"Removed Columns1", "Custom.2", {"Description1", "Description2"}, {"Description1", "Description2"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded Custom.2",{"Column1", "Custom.3", "Description1", "Description2"}),
    #"Removed Columns2" = Table.RemoveColumns(#"Reordered Columns",{"Column1"})
in
    #"Removed Columns2"

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

My custom visualization projects

Plotting Live Sound: Viz1

Beautiful News:Viz1, Viz2, Viz3

Visual Capitalist: Working Hrs

Others:Easing Graph, Animated Calendar

View solution in original post

v-lili6-msft · ‎02-10-2020

hi @D3K

You may refer to this similar post too:

https://community.powerbi.com/t5/Desktop/Expand-value-from-table/td-p/214838

Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.