Alternate to Pivot column when using direct query, splitting out custom field columns

[sscollins]

I have data coming in through a direct query, but I have a couple of tables that hold all custom fields in one column for the custom field name, and another for the custom field's values. I need the field name column to be the column header and the values to populate. I cannot use the Pivot Column due to the direct query connection. Has anyone encountered similar issues, is this a case for needing a script to split up? I would even settle for choosing a couple of columns and not all of them.

[v-yalanwu-msft · Accepted solution]

Hi， @sscollins ；

For DirectQuery,

However, upon load, no data is imported into the Power BI store. so if you want to split in power query you must change the mode to import.

Or you consider using DAX to split; Or create a new table in Power Query to copy the columns that need to be split, and then split, and join the relationship as well

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