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 ,

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.