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