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 Sou...
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
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