Forum Discussion

KunalMathkar's avatar
KunalMathkar
Frequent Visitor
1 year ago
Solved

I need support for write or rectify query

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

 

General Comment
Need Help
Tips and Tricks
  • KunalMathkar's avatar
    KunalMathkar
    1 year ago

    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

9 Replies

  • jgeddes's avatar
    jgeddes
    Super User
    1 year ago

    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's avatar
      KunalMathkar
      Frequent Visitor
      1 year ago

      i changed acording to your suggestion , now below error

       

      • jgeddes's avatar
        jgeddes
        Super User
        1 year ago

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

  • KunalMathkar's avatar
    KunalMathkar
    Frequent Visitor
    1 year ago

    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's avatar
      jgeddes
      Super User
      1 year ago

      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
  • v-dineshya's avatar
    v-dineshya
    Community Support
    1 year ago

    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's avatar
      v-dineshya
      Community Support
      1 year ago

      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's avatar
        v-dineshya
        Community Support
        1 year ago

        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's avatar
    KunalMathkar
    Frequent Visitor
    1 year ago

    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