I need support for write or rectify query

[KunalMathkar]

I have connected power bi with clickhouse database.

my table name is  "jira_issues"

i have explained query below

i have pasted this in chatgpt and i got elow response as query -

let
Source = jira_issues,

// Define your custom field mappings here
CustomFieldMap = [
"customfield_10000" = "Development",
"customfield_10103" = "Original story points",
// Add more mappings as needed
],

// Get all column names
ColumnNames = Table.ColumnNames(Source),

// Generate new column names based on mapping
RenamedColumnsList = List.Transform(ColumnNames, (colName) =>
let
// Try to find a matching customfield key in the column name
matchingKey = List.First(List.Select(Record.FieldNames(CustomFieldMap), each Text.StartsWith(colName, _)), null),
newName = if matchingKey <> null then
Text.Replace(colName, matchingKey, Record.Field(CustomFieldMap, matchingKey))
else
colName
in
{colName, newName}
),

// Apply renaming
RenamedTable = Table.RenameColumns(Source, RenamedColumnsList)
in
RenamedTable

after writing this qury in transform query editor in power bi i am getting below error but you can see that value is present in column name.

images for reference - 

help me to resolve this so i can rename all column names using query.if above query is wrong let me know right query

[KunalMathkar · Accepted solution]

yes i got ny correct query and query is here

let

    // Connect to the ClickHouse database

    Source = ClickHouse.Database("10.1.1.1", 1, null, []),

    // Navigate to the specific database and table

    DB = Source{[Name = "abc", Kind = "Database"]}[Data],

    JiraIssues = DB{[Name = "jira", Kind = "Table"]}[Data],

    // Define the mapping of custom field names to friendly names

    CustomFieldMap = [

        customfield_10000 = "Request participants",

        customfield_10103 = "Account"

        // Add more mappings here, separated by commas

    ],

    // Get all column names from the table

    ColumnNames = Table.ColumnNames(JiraIssues),

    // Generate a list of {oldName, newName} pairs based on the mapping

    RenamedColumnsList = List.Transform(ColumnNames, (colName) =>

        let

            matchingKey = List.First(List.Select(Record.FieldNames(CustomFieldMap), each Text.StartsWith(colName, _)), null),

            newName = if matchingKey <> null then

                        Text.Replace(colName, matchingKey, Record.Field(CustomFieldMap, matchingKey))

                      else

                        colName

        in

            {colName, newName}

    ),

    // Apply the renaming to the table

    RenamedTable = Table.RenameColumns(JiraIssues, RenamedColumnsList)

in

    RenamedTable

[v-dineshya]

Hi @KunalMathkar ,

Thank you for reaching out to the Microsoft Community Forum.

Please refer the M code.

let
Source = jira_issues,

CustomFieldMap = [
"customfield_10000" = "Development",
"customfield_10103" = "Rank"
],

ColumnNames = Table.ColumnNames(Source),

RenamedColumnsList = List.Transform(ColumnNames, (colName) =>
let
matchingKey = List.First(List.Select(Record.FieldNames(CustomFieldMap), each Text.Contains(colName, _)), null),
newName = if matchingKey <> null then
Text.Replace(colName, matchingKey, Record.Field(CustomFieldMap, matchingKey))
else
colName
in
{colName, newName}
),

RenamedTable = Table.RenameColumns(Source, RenamedColumnsList)
in
RenamedTable

Tested the M code with sample code.

1. Created sample data, refer the snap.

2.  Created M code for sample code.

let

Source = #table(
{"customfield_10000", "customfield_10103_category", "customfield_10103_category_key", "subtask_keys", "customfield_engagement"},
{
{"dev_1", "A", "A1", "STK1", "ENG1"},
{"dev_2", "B", "B2", "STK2", "ENG2"}
}
),

CustomFieldMap = Record.FromList(
{"Development", "Rank"},
{"customfield_10000", "customfield_10103"}
),

ColumnNames = Table.ColumnNames(Source),

RenamedColumnsList = List.Transform(ColumnNames, (colName) =>
let
matchingKey = List.First(List.Select(Record.FieldNames(CustomFieldMap), each Text.Contains(colName, _)), null),
newName = if matchingKey <> null then
Text.Replace(colName, matchingKey, Record.Field(CustomFieldMap, matchingKey))
else
colName
in
{colName, newName}
),

RenamedTable = Table.RenameColumns(Source, RenamedColumnsList)
in
RenamedTable

Sample code output.

Please refer output snap and PBIX file.

If my response has resolved your query, please mark it as the "Accepted Solution" to assist others. Additionally, a "Kudos" would be appreciated if you found my response helpful.

Thank you

[v-dineshya]

Hi @KunalMathkar ,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet.do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Thank you.

[v-dineshya]

Hi @KunalMathkar ,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet.do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Thank you.

[KunalMathkar]

let
Source = jira_issues,

// Define your custom field mappings here
CustomFieldMap = [
customfield_10000 = "Development",
customfield_10103 = "Original story points"
// Add more mappings as needed
],

// Get all column names
ColumnNames = Table.ColumnNames(Source),

// Generate new column names based on mapping
RenamedColumnsList = List.Transform(ColumnNames, (colName) =>
let
// Try to find a matching customfield key in the column name
matchingKey = List.First(List.Select(Record.FieldNames(CustomFieldMap), each Text.StartsWith(colName, _)), null),
newName = if matchingKey <> null then
Text.Replace(colName, matchingKey, Record.Field(CustomFieldMap, matchingKey))
else
colName
in
{colName, newName}
),

// Apply renaming
RenamedTable = Table.RenameColumns(Source, RenamedColumnsList)
in

[jgeddes]

When I test with a sample table I cannot replicate a cyclic error. That leads me to believe the error is being generated in your source table.
Here is an alternative method you can try if you wish. If the cyclic error persists you will have to look at your source for the error.

let
    Source =
    /* Debugging Table
    #table(
        {"customfield_10000", "customfield_10100", "customfield_10103"},
        {
            {1,2,3},
            {4,5,6}
        }
    ),*/ 
    jira_issues, 
    // Define your custom field mappings here 
    CustomFieldMap = 
    { 
        {"customfield_10000", "Development"}, 
        {"customfield_10103", "Original story points"} 
    // Add more mappings as needed 
    },
    RenameColumns =
    Table.RenameColumns(
        Source,
        CustomFieldMap,
        MissingField.Ignore
    )
in
    RenameColumns

[jgeddes]

In your step "CustomFieldMap" you are creating a record. In a record the field names do not require quotes. If you change your step to read,

CustomFieldMap = [
customfield_10000 = "Development",
customfield_10103 = "Original story points"
// Add more mappings as needed
]

you should get rid of the error. Also I removed the trailing comma from the customfield_10103 line as it is the last field in the record.

[KunalMathkar]

i changed acording to your suggestion , now below error

[jgeddes]

What does your complete code in the advanced editor look like now?