Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi
I have this syntax which works great and allows me to expand all columns from a merge table named 'TechRates' that start with "UK"
#"Merged Queries" = Table.NestedJoin(#"Filtered Rows2", {"Technician Type"}, TechRates, {"US- TechType"}, "Technicians", JoinKind.LeftOuter),
DocColumnList = List.Buffer(List.Select(Table.ColumnNames(TechRates), each Text.StartsWith(_ , "UK") )),
#"Expanded Technicians" = Table.ExpandTableColumn(#"Merged Queries", "Technicians",DocColumnList),
I would now like to make this dynamic using a text field in the base table named ABC Division as shown
#"Merged Queries" = Table.NestedJoin(#"Filtered Rows2", {"Technician Type"}, TechRates, {"US- TechType"}, "Technicians", JoinKind.LeftOuter),
DocColumnList = List.Buffer(List.Select(Table.ColumnNames(TechRates), each Text.StartsWith(_ , [ABC Division]) )),
#"Expanded Technicians" = Table.ExpandTableColumn(#"Merged Queries", "Technicians",DocColumnList),
but I get this error
Expression.Error: We cannot apply field access to the type Text.
Details:
Value=US- TechType
Key=ABC Division
What I cannot figure out is the syntax needed to reference the column name ABC Division as a text value so it can be used.
Any help would be appreciated. Thanks
Solved! Go to Solution.
Hi @Richard_Halsall,
Do all rows in the ABC Division column of the base table contain the same text?
If so, try this to get the initial value: BaseTableName{0}[ABC Division] this syntax can also be generated by the UI when drilling down into a cell's value.
DocColumnList = List.Buffer(List.Select(Table.ColumnNames(TechRates), each Text.StartsWith(_ , BaseTableName{0}[ABC Division]) ))
I hope this is helpful
Hi @Richard_Halsall,
Do all rows in the ABC Division column of the base table contain the same text?
If so, try this to get the initial value: BaseTableName{0}[ABC Division] this syntax can also be generated by the UI when drilling down into a cell's value.
DocColumnList = List.Buffer(List.Select(Table.ColumnNames(TechRates), each Text.StartsWith(_ , BaseTableName{0}[ABC Division]) ))
I hope this is helpful
Many thanks that worked perfectly