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

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