Cannot expand table from merged query

[Anonymous]

I'm getting this error message at the code highlighted with red:
"Expression.Error: We cannot convert a value of type Table to type Text.
Details:
Value=[Table]
Type=[Type]"

"UDO til Profitcenter" 1:many "source table"

let
Source = SharePoint.Files("https://xxxxxxxxxxxxxxx.com", [ApiVersion = 15]),
#"Filtered Rows" = Table.SelectRows(Source, each ([Folder Path] = "https:/xxxxxxxxxxxxxxxxx.com")),
#"Filtered Hidden Files1" = Table.SelectRows(#"Filtered Rows", each [Attributes]?[Hidden]? <> true),
#"Filtered Hidden Files2" = Table.SelectRows(#"Filtered Hidden Files1", each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files2", "Transform File (8)", each #"Transform File (8)"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File (8)"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File (8)", Table.ColumnNames(#"Transform File (8)"(#"Sample File (8)"))),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Name", type text}, {"Table", type any}}),
#"Expanded Table" = Table.ExpandTableColumn(#"Changed Type", "Table", {"NUMMER", "VERTRAGS_NR", "KUNDE_NR", "KUNDE_NAME", "PARTNER_NR", "MB", "ABR_ZEITRAUM_VON", "ABR_ZEITRAUM_BIS", "WHRG", "LS_DATUM", "LS_ART", "EINHEIT", "MENGE", "PREIS_ZU_VERR", "VERSION", "BELEG_DATUM", "BELEG_NR", "BELEG_POS", "UST_KZ", "KONTO", "RG_BETRAG", "SD_NR", "VAT_DATUM", "SYS_VERR", "DEBITOREN_NR", "AAR_NR", "REKL_ERG", "REKL_NR", "BB_ID", "COIA_NR", "COIA_BEZ", "BILL_REQ_NR", "BEMERKUNG", "NETTO_MDT_WHRG", "FAKTOR_MDT_WHRG", "PO_NUMMER", "FLOW_DESC"}, {"NUMMER", "VERTRAGS_NR", "KUNDE_NR", "KUNDE_NAME", "PARTNER_NR", "MB", "ABR_ZEITRAUM_VON", "ABR_ZEITRAUM_BIS", "WHRG", "LS_DATUM", "LS_ART", "EINHEIT", "MENGE", "PREIS_ZU_VERR", "VERSION", "BELEG_DATUM", "BELEG_NR", "BELEG_POS", "UST_KZ", "KONTO", "RG_BETRAG", "SD_NR", "VAT_DATUM", "SYS_VERR", "DEBITOREN_NR", "AAR_NR", "REKL_ERG", "REKL_NR", "BB_ID", "COIA_NR", "COIA_BEZ", "BILL_REQ_NR", "BEMERKUNG", "NETTO_MDT_WHRG", "FAKTOR_MDT_WHRG", "PO_NUMMER", "FLOW_DESC"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Table",{"Source.Name"}),
#"Filtered Rows1" = Table.SelectRows(#"Removed Columns", each Text.Contains([LS_ART], "UD0")),
#"Split Column by Position" = Table.SplitColumn(#"Filtered Rows1", "LS_ART", Splitter.SplitTextByPositions({0, 7}, true), {"LS_ART.1", "LS_ART.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Position",{{"LS_ART.1", type text}, {"LS_ART.2", type text}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"LS_ART.2", "UD0"}}),
#"Trimmed Text" = Table.TransformColumns(#"Renamed Columns",{{"UD0", Text.Trim, type text}}),
#"Expanded BEMERKUNG" = Table.ExpandTableColumn(#"Trimmed Text", "BEMERKUNG", {"Element:Text"}, {"Element:Text"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Expanded BEMERKUNG",{{"PREIS_ZU_VERR", type number}, {"RG_BETRAG", type number}, {"NETTO_MDT_WHRG", type number}}),
#"Changed Type3" = Table.TransformColumnTypes(#"Changed Type2",{{"ABR_ZEITRAUM_VON", type date}}),
#"Added Custom" = Table.AddColumn(#"Changed Type3", "Month & Year & UD0", each Text.From(Date.Month([ABR_ZEITRAUM_VON])) & "-" & Text.From(Date.Year([ABR_ZEITRAUM_VON])) & "-" & [UD0]),
#"Changed Type4" = Table.TransformColumnTypes(#"Added Custom",{{"Month & Year & UD0", type text}}),
#"Merged Queries" = Table.NestedJoin(#"Changed Type4", {"Month & Year & UD0"}, #"UDO til Profitcenter", {"Måned & År & Profitcenter"}, "UDO til Profitcenter", JoinKind.LeftOuter),
#"Expanded UDO til Profitcenter" = Table.ExpandTableColumn(#"Merged Queries", "UDO til Profitcenter", {"Profitcenter Number"}, {"Profitcenter Number"}),
#"Renamed Columns2" = Table.RenameColumns(#"Expanded UDO til Profitcenter",{{"Profitcenter Number", "Profitcenter nummer"}}),
#"Added Custom1" = Table.AddColumn(#"Renamed Columns2", "ID - Profitcenter", each Text.From(Date.Month([ABR_ZEITRAUM_VON])) & "-" & Text.From(Date.Year([ABR_ZEITRAUM_VON])) & "-" & [Profitcenter nummer])
in
#"Added Custom1"

[Anonymous · Accepted solution]

I found the problem.
Apparently there was a column which had both text values and tables, in the same column. 
Hence created issues throughout the query.

[Anonymous]

Hi, @Anonymous 

May I ask if you have gotten this issue resolved? If it is solved, please share your solution and accept it as solution, it will be helpful for other members of the community who have similar problems as yours to solve it faster.

If it is not resolved, I hope you will provide the full .pbix file via OneDrive or SharePoint. Please be careful to remove all sensitive information and we will do our best to provide ideas for your issue.

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Fen Ling,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Unfortunately the proposed solution didn't work. 
It created a new column with a list of the column names from the table I wanted to merge with the source table.
I don't want to expand the column names, but I only want to expand one column ("Profitcenter nummer") and its values.
I'll try to create a simplified pbix file as well.

[Anonymous]

Hi, @Anonymous 

I am glad to help you.   

According to your description, you cannot expand table from merged query? 

If I understand you correctly, then you can refer to my solution.  

Based on your error message, it appears that the “UDO til Profitcenter” column is treated as a table when you expand it, but the code is trying to expand it to text. This may be because the column contains nested tables instead of the expected text values. 

You can check the type of the “UDO til Profitcenter” column before expanding it: 

If it is indeed a nested table, you can try adding the following code on top of the red boxed section of code in the image: 

#"Inspected Column" = Table.AddColumn(#"Merged Queries", "UDO til Profitcenter (Inspect)", each Table.ColumnNames([UDO til Profitcenter])), 

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you. 

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Fen Ling,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.