Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Rename multiple columns via a function or bulk edit

I have a problem when I bring my data into PowerBi it removes all the spaces in the column name and it assigns the column name as caml casing.
for example one of the questions could be: "I Have Questions On Three" and it replaces it with "IhaveQuestionsOnThree". I could just rename them one by one but that would mean I have to do that alot of times since I have many dashboards I am dealing with.

 

Here is one of the few columns as an example: 

 

rename.png

 

I have found ways around this with excel vba function:

 

Function Cher(s As String)
With CreateObject("vbscript.regexp")
.Global = True
.Pattern = "(\w)(?=[A-Z])"
Cher = .Replace(s, "$1 ")
End With
End Function

 

or Javascript by simply finding the capital letters and adding a space before it. so my question is... Is it possible to run a function against selected columns to rename them? is there another way? Am I overthinking it? 

 

Thank you in advance.

2 ACCEPTED SOLUTIONS

Hi @Anonymous

 

My apologies. I missed your reply.

 

Please see the attached file. I modified your query.

Please go to Query Editor and see Table1

 

 

 


Regards
Zubair

Please try my custom visuals

View solution in original post

@Anonymous

 

Did it work?


Regards
Zubair

Please try my custom visuals

View solution in original post

9 REPLIES 9
dbp
Regular Visitor

I found the existing solution very difficult to work with.

 

Here is a PowerQuery that works out of the box:

 

 

 

let
    // Specify the source table or query
    source = YourSourceTableOrQuery,

    // Define the renaming rule
    renameRule = (oldName) => "New_" & oldName,

    // Get the list of existing column names
    existingColumnNames = Table.ColumnNames(source),

    // Apply the renaming rule to each column name
    renamedColumnNames = List.Transform(existingColumnNames, renameRule),

    // Combine the original and renamed column names into pairs
    columnPairs = List.Zip({existingColumnNames, renamedColumnNames}),

    // Apply the column renaming
    renamedColumns = Table.RenameColumns(source, columnPairs)
in
    renamedColumns

 

 

 

v-jiascu-msft
Employee
Employee

Hi @Anonymous,

 

There is a newer version of API. Did you try it? Did you change it from 15 to 14 manually?

 

Source = SharePoint.Tables("https://mytenant.sharepoint.com/sites/sitecollection/", [ApiVersion = 14])

 

Best Regards,
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

yes, unfurtunately the newest version has a bug and it cuts off all the column names and that is why I used version 14. 

Zubair_Muhammad
Community Champion
Community Champion

Hi @Anonymous

 

Try this. I think it will do the job but will be slow.

Please see attached file as well with your sample data

 

First create a new table using >>New Sourec>>Blank Query.

Call it Modifiers

This will be used to do modifications

 

 

let
    Custom1 = {"A".."Z"},
    Custom2 = List.Transform(Custom1,each " "&_),
    Table1=Table.FromColumns({Custom1,Custom2},{"Current","Replacement"}),
   Custom3=Table.AddColumn(Table.FromList({0..9}, Splitter.SplitByNothing(), null, null, ExtraValues.Error), "Index",each 1),
    Custom4=Table.AddColumn(Table.FromList({"a".."z"}, Splitter.SplitByNothing(), null, null, ExtraValues.Error), "Index",each 1),
    Custom5 = Table.NestedJoin(Custom4,{"Index"},Custom3,{"Index"},"Table1",JoinKind.LeftOuter),
    #"Expanded Table1" = Table.ExpandTableColumn(Custom5, "Table1", {"Column1"}, {"Column1.1"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Table1",{"Index"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Column1", "Alphabets"}, {"Column1.1", "Numbers"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Replacement", each [Alphabets]&" "&Text.From([Numbers])),
    Table2 = Table.CombineColumns(Table.TransformColumnTypes(#"Added Custom", {{"Numbers", type text}}, "en-US"),{"Alphabets", "Numbers"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Current"),
    Custom6 = Table.Combine({Table1,Table2})
in
   Custom6

Now in your original table we can use

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlfSUTIBYgsgNlWK1YlWMoKKmAGxOVjEECpvaAAkLMFCMC3GYBwbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [IfAnAdultWasTheAdultDeterminedLowIncome = _t, WasDocumentationInTheCaseFileThatParticipantWas18Years = _t, ILovePowerBi = _t, ILoveExcelToo2 = _t]),
    #"Demoted Headers" = Table.DemoteHeaders(Source),
    #"Changed Type" = Table.TransformColumnTypes(#"Demoted Headers",{{"Column1", type any}, {"Column2", type any}, {"Column3", type any}, {"Column4", type any}}),
    TT = Table.Transpose(#"Changed Type"),
    Final=List.Last(List.Generate(
        ()=>[x=0,y=Table.ReplaceValue(TT,Modifiers[Current]{x},Modifiers[Replacement]{x},Replacer.ReplaceText,{"Column1"}),z=[y]],
        each [x]<Table.RowCount(Modifiers),
        each [x=[x]+1,y=Table.ReplaceValue(z,Modifiers[Current]{x},Modifiers[Replacement]{x},Replacer.ReplaceText,{"Column1"}),z=[y]],
        each [y])),
    TransposedTable = Table.Transpose(Final),
    PromotedHeaders = Table.PromoteHeaders(TransposedTable, [PromoteAllScalars=true])

 
    
in
PromotedHeaders

 

 


Regards
Zubair

Please try my custom visuals
Anonymous
Not applicable

Hello @Zubair_Muhammad,

 

Thanks for the help! I am faily new in the powerBi world and I am not exacly sure how to apply this method with my current data connection.

 

Here is what I have:

 

let
Source = SharePoint.Tables("https://mytenant.sharepoint.com/sites/sitecollection/", [ApiVersion = 14]),
WIOA = Source{[Name="WIOA"]}[Content],
#"Removed Columns" = Table.RemoveColumns(WIOA1,{"Comments", "ContentType", "Modified", "Created", "CreatedById", "ModifiedById", "Owshiddenversion", "Version", "Path", "IndicateWhetherTheParticipantIsAnAdultAOrDislocatedWorkerDW", "IfAnAdultWasTheAdultDeterminedLowIncomeAsDescribedInTheLocalPlanAndWasThereDocumentationInTheFileToSupportTheLowIncomeDeterminat", "IfADislocatedWorkerDidTheCaseFileContainDocumentationOfLayOffTerminationPlantClosureRecentlySeparatedVeteranOrSpouseOfARecentlyS", "WasDocumentationInTheCaseFileThatTheParticipantWas18YearsOfAgeOrOlderAtRegistration", "WasDocumentationInTheCaseFileOfUSCitizenshipOrAuthorizationToWorkInTheUS", "IfRequiredWasDocumentationInTheCaseFileOfSelectiveServiceRegistrationOrAnAllowableExemption", "WasTheParticipantAnEmployedWorkerIncumbentWorkerAtTheTimeOfRegistration", "IfYesTo7AndTheParticipantWasNotReferredByAnEmployerIsThereDocumentationInTheCaseFileIndicatingThatTheParticipantWasNotEarningASe", "IfYesTo7AndTheParticipantWasReferredByAnEmployerIsThereDocumentationInTheCaseFileFromTheEmployerIndicatingTheParticipantWasInNee", "IfTheParticipantWasAVeteranDidTheFileContainDocumentationToVerifyVeteranStatus", "IfTheParticipantWasAnEligibleSpouseOfAVeteranDidTheFileContainDocumentationToVerifyEligibleSpouseOfAVeteranStatus", "WasASignedAndDatedGrievanceComplaintAndEEODiscriminationFormInTheParticipantsCaseFile", "IfYesTo12DidTheGrievanceComplaintAndEEODiscriminationFormIncludeCorrectNamesAndAddressesForFilingAGrievanceAppealOrEEOComplaint", "WasABasicCareerServiceActivityEnteredInTheStatesMIS", "IfYesTo14WasDocumentationInTheCaseFileOfTheBasicCareerServiceProvided", "WasAnIndividualizedCareerServiceEnteredInTheStatesMIS", "IfYesTo16WasDocumentationInTheCaseFileOfADeterminationOfNeedForIndividualizedServicesToObtainOrRetainEmploymentLeadingToSelfSuff", "IfYesTo16WasDocumentationInTheCaseFileOfTheIndividualizedCareerServiceProvided", "WasTheParticipantProvidedAWorkExperienceWEService", "WasAWEActivityEnteredInTheStatesMIS", "IfYesTo20WasAWETrainingAgreementExecutedBetweenTheEmployerAndTheLWDBForTheParticipantsTraining", "WasDocumentationInTheCaseFileThatTheWEStartDateWasOnOrAfterTheEmployersWETrainingAgreementEffectiveDate", "WasThisAPaidWE", "IfYesTo23WasTheParticipantPaidTheWageStatedInTheAgreementAndWereFLSARequirementsMet", "WasTheWETrainingProvidedAsDescribedInTheWETrainingPlan", "IfATransitionalJobWasTheWorkExperienceCombinedWithComprehensiveCareerServicesAndSupportServices", "WasAnOccupationalSkillsOSRegisteredApprenticeshipRAPreApprenticeshipPASkillsUpgradeAndRetrainingSUROrAdultEducationAndLiteracyAE", "IfYesTo27WasDocumentationInTheCaseFileOfADeterminationOfNeedForTrainingServicesAfterAnInterviewEvaluationOrAssessmentAndCareerPl", "IfAnOSRAPASUROrAELActivityWasProvidedWasTheTrainingInALocalStateDemandOccupation", "WasTheTrainingProviderOnTheLocalStateApprovedEligibleTrainingProviderListETPL", "IfAnIndividualTrainingAccountITAWasUtilizedWereITACostsRecordedInTheTrainingEnrollmentCostTableInTheStatesMIS", "IfARAActivityWasProvidedWasAnOJTActivityEnteredInTheStatesMIS", "IfAnAELActivityWasProvidedAndTitleIAdultAndDislocatedWorkerFundsWereUsedWasTheAELActivityDoneConcurrentlyOrInCoordinationWithAnA", "WasOnTheJobOJTCustomizedTrainingCTOrIncumbentWorkerTrainingIWTProvidedToTheParticipant", "IfYesTo34IndicateTheTypeOfTrainingProvidedOJTCTOrIWT", "IfIWTWasThereDocumentationOfAnEstablishedEmploymentHistoryWithTheEmployerOfSixMonthsOrMoreOrWasTheParticipantPartOfATrainingGrou", "WasAnOJTCTOrIWTAgreementExecutedBetweenTheEmployerOrARAProgramSponsorAndTheRegionForTheParticipantsTrainingPosition", "IsDocumentationInTheCaseFileOfTheReferralToTheOJTEmployer", "IfYesTo38DoesTheJobTitleOnTheReferralMatchTheOccupationListedOnTheParticipantsIEPOrCaseNotes", "IsDocumentationInTheCaseFileThatTheParticipantsOJTCTIWTStartDateWasOnOrAfterTheEmployersOJTCTIWTContractEffectiveDate", "DidTheFileContainDetailsOfTheSkillsToBeAttainedTheDurationOfTheTrainingAndTheWageRateOrTheEstablishedNonFederalReimbursementShar", "WasTheTrainingProvidedAsDescribedInTheOJTCTIWTAgreement", "WasACredentialAttainmentEnteredInTheMIS", "IfYesTo43WasDocumentationInTheParticipantsCaseFileToSupportTheCredential", "IfYesTo44DidTheCredentialAttainmentDateAndTypeMatchTheCredentialAttainmentInformationEnteredInTheMIS", "WasASupportiveServiceActivityEnteredInMIS", "IfYesTo46WasThereDocumentationInTheParticipantCaseFileToVerifyTheSupportiveServiceProvided", "DidTheSupportiveServiceActivityDocumentedInTheCaseFileMatchTheSupportiveServiceActivityEnteredInTheMIS", "WasDocumentationInTheCaseFileToShowThatTheSupportiveServicesWereIssuedInAccordanceWithLocalPolicy", "WasTheParticipantExitedInTheMIS", "IfYesTo50AndTheParticipantExitedWithUnsubsidizedEmploymentWasDocumentationInTheCaseFileToVerifyTheEmploymentStartDateAndWageInfo", "IfYesTo51WasTheEmploymentInformationAccuratelyEnteredInTheMIS", "WasAFollowUpServiceEnteredInTheStatesMIS", "IfYesTo53WasDocumentationInTheCaseFileOfADeterminationOfNeedForFollowUpServices", "IfYesTo53WasDocumentationInTheCaseFileOfTheFollowUpServiceProvidedToTheParticipant", "WereRequiredFollowUpsConductedForEachOfThe1st2nd3rdAnd4thQuartersAfterExitIntervalsAsApplicable", "IfYesTo57WereTheFollowUpsConductedByTheDueDateIndicatedInTheFollowUpTableInTheMIS", "WasEmploymentInformationCorrectlyEnteredInTheFollowUpFieldsInEFMForEachApplicableQuarterAndProperlyVerified", "IsTheApplicationProfileInTheStateLaborExchangeSystemExEFMOrWITUpToDateAndRelevant", "IsTheIndividualEmploymentPlanSignedAndOnFile", "IsTheIndividualEmploymentPlanCompleteAndUpOtDate", "CounselorNotesAreCurrentSufficientlyDetailedAndWithinPolicy", "IsThereEvidenceOfOngoingAssessment", "IsTAADocumentedIfCoEnrolled", "AreAppropriateAttendanceTimesheetsOnFile", "CreatedBy", "ModifiedBy", "Attachments", "StaffId", "IDNumber", "OfNA", "OfNo", "OfYes", "RegionId", "CareerCenterId", "ReviewerNameId", "ContentTypeID", "Title", "ComplianceAssetId", "IndicateWhetherTheParticipantIsAnAdultAOrDislocatedWorkerDWValue"}),
#"Expanded Region" = Table.ExpandRecordColumn(#"Removed Columns", "Region", {"Title"}, {"Region.Title"}),
#"Removed Columns1" = Table.RemoveColumns(#"Expanded Region",{"ReviewerName"}),
#"Expanded CareerCenter" = Table.ExpandRecordColumn(#"Removed Columns1", "CareerCenter", {"Title"}, {"CareerCenter.Title"}),
#"Expanded Staff" = Table.ExpandRecordColumn(#"Expanded CareerCenter", "Staff", {"FullName"}, {"Staff.FullName"}),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Staff",{{"DateOfReview", type date}}),
#"Unpivoted Only Selected Columns" = Table.Unpivot(#"Changed Type", {"IfAnAdultWasTheAdultDeterminedLowIncomeAsDescribedInTheLocalPlanAndWasThereDocumentationInTheFileToSupportTheLowIncomeDeterminatValue", "IfADislocatedWorkerDidTheCaseFileContainDocumentationOfLayOffTerminationPlantClosureRecentlySeparatedVeteranOrSpouseOfARecentlySValue", "WasDocumentationInTheCaseFileThatTheParticipantWas18YearsOfAgeOrOlderAtRegistrationValue", "WasDocumentationInTheCaseFileOfUSCitizenshipOrAuthorizationToWorkInTheUSValue", "IfRequiredWasDocumentationInTheCaseFileOfSelectiveServiceRegistrationOrAnAllowableExemptionValue", "WasTheParticipantAnEmployedWorkerIncumbentWorkerAtTheTimeOfRegistrationValue", "IfYesTo7AndTheParticipantWasNotReferredByAnEmployerIsThereDocumentationInTheCaseFileIndicatingThatTheParticipantWasNotEarningASeValue", "IfYesTo7AndTheParticipantWasReferredByAnEmployerIsThereDocumentationInTheCaseFileFromTheEmployerIndicatingTheParticipantWasInNeeValue", "IfTheParticipantWasAVeteranDidTheFileContainDocumentationToVerifyVeteranStatusValue", "IfTheParticipantWasAnEligibleSpouseOfAVeteranDidTheFileContainDocumentationToVerifyEligibleSpouseOfAVeteranStatusValue", "WasASignedAndDatedGrievanceComplaintAndEEODiscriminationFormInTheParticipantsCaseFileValue", "IfYesTo12DidTheGrievanceComplaintAndEEODiscriminationFormIncludeCorrectNamesAndAddressesForFilingAGrievanceAppealOrEEOComplaintValue", "WasABasicCareerServiceActivityEnteredInTheStatesMISValue", "IfYesTo14WasDocumentationInTheCaseFileOfTheBasicCareerServiceProvidedValue", "WasAnIndividualizedCareerServiceEnteredInTheStatesMISValue", "IfYesTo16WasDocumentationInTheCaseFileOfADeterminationOfNeedForIndividualizedServicesToObtainOrRetainEmploymentLeadingToSelfSuffValue", "IfYesTo16WasDocumentationInTheCaseFileOfTheIndividualizedCareerServiceProvidedValue", "WasTheParticipantProvidedAWorkExperienceWEServiceValue", "WasAWEActivityEnteredInTheStatesMISValue", "IfYesTo20WasAWETrainingAgreementExecutedBetweenTheEmployerAndTheLWDBForTheParticipantsTrainingValue", "WasDocumentationInTheCaseFileThatTheWEStartDateWasOnOrAfterTheEmployersWETrainingAgreementEffectiveDateValue", "WasThisAPaidWEValue", "IfYesTo23WasTheParticipantPaidTheWageStatedInTheAgreementAndWereFLSARequirementsMetValue", "WasTheWETrainingProvidedAsDescribedInTheWETrainingPlanValue", "IfATransitionalJobWasTheWorkExperienceCombinedWithComprehensiveCareerServicesAndSupportServicesValue", "WasAnOccupationalSkillsOSRegisteredApprenticeshipRAPreApprenticeshipPASkillsUpgradeAndRetrainingSUROrAdultEducationAndLiteracyAEValue", "IfYesTo27WasDocumentationInTheCaseFileOfADeterminationOfNeedForTrainingServicesAfterAnInterviewEvaluationOrAssessmentAndCareerPlValue", "IfAnOSRAPASUROrAELActivityWasProvidedWasTheTrainingInALocalStateDemandOccupationValue", "WasTheTrainingProviderOnTheLocalStateApprovedEligibleTrainingProviderListETPLValue", "IfAnIndividualTrainingAccountITAWasUtilizedWereITACostsRecordedInTheTrainingEnrollmentCostTableInTheStatesMISValue", "IfARAActivityWasProvidedWasAnOJTActivityEnteredInTheStatesMISValue", "IfAnAELActivityWasProvidedAndTitleIAdultAndDislocatedWorkerFundsWereUsedWasTheAELActivityDoneConcurrentlyOrInCoordinationWithAnAValue", "WasOnTheJobOJTCustomizedTrainingCTOrIncumbentWorkerTrainingIWTProvidedToTheParticipantValue", "IfYesTo34IndicateTheTypeOfTrainingProvidedOJTCTOrIWTValue", "IfIWTWasThereDocumentationOfAnEstablishedEmploymentHistoryWithTheEmployerOfSixMonthsOrMoreOrWasTheParticipantPartOfATrainingGrouValue", "WasAnOJTCTOrIWTAgreementExecutedBetweenTheEmployerOrARAProgramSponsorAndTheRegionForTheParticipantsTrainingPositionValue", "IsDocumentationInTheCaseFileOfTheReferralToTheOJTEmployerValue", "IfYesTo38DoesTheJobTitleOnTheReferralMatchTheOccupationListedOnTheParticipantsIEPOrCaseNotesValue", "IsDocumentationInTheCaseFileThatTheParticipantsOJTCTIWTStartDateWasOnOrAfterTheEmployersOJTCTIWTContractEffectiveDateValue", "DidTheFileContainDetailsOfTheSkillsToBeAttainedTheDurationOfTheTrainingAndTheWageRateOrTheEstablishedNonFederalReimbursementSharValue", "WasTheTrainingProvidedAsDescribedInTheOJTCTIWTAgreementValue", "WasACredentialAttainmentEnteredInTheMISValue", "IfYesTo43WasDocumentationInTheParticipantsCaseFileToSupportTheCredentialValue", "IfYesTo44DidTheCredentialAttainmentDateAndTypeMatchTheCredentialAttainmentInformationEnteredInTheMISValue", "WasASupportiveServiceActivityEnteredInMISValue", "IfYesTo46WasThereDocumentationInTheParticipantCaseFileToVerifyTheSupportiveServiceProvidedValue", "DidTheSupportiveServiceActivityDocumentedInTheCaseFileMatchTheSupportiveServiceActivityEnteredInTheMISValue", "WasDocumentationInTheCaseFileToShowThatTheSupportiveServicesWereIssuedInAccordanceWithLocalPolicyValue", "WasTheParticipantExitedInTheMISValue", "IfYesTo50AndTheParticipantExitedWithUnsubsidizedEmploymentWasDocumentationInTheCaseFileToVerifyTheEmploymentStartDateAndWageInfoValue", "IfYesTo51WasTheEmploymentInformationAccuratelyEnteredInTheMISValue", "WasAFollowUpServiceEnteredInTheStatesMISValue", "IfYesTo53WasDocumentationInTheCaseFileOfADeterminationOfNeedForFollowUpServicesValue", "IfYesTo53WasDocumentationInTheCaseFileOfTheFollowUpServiceProvidedToTheParticipantValue", "WereRequiredFollowUpsConductedForEachOfThe1st2nd3rdAnd4thQuartersAfterExitIntervalsAsApplicableValue", "IfYesTo57WereTheFollowUpsConductedByTheDueDateIndicatedInTheFollowUpTableInTheMISValue", "WasEmploymentInformationCorrectlyEnteredInTheFollowUpFieldsInEFMForEachApplicableQuarterAndProperlyVerifiedValue", "IsTheApplicationProfileInTheStateLaborExchangeSystemExEFMOrWITUpToDateAndRelevantValue", "IsTheIndividualEmploymentPlanSignedAndOnFileValue", "IsTheIndividualEmploymentPlanCompleteAndUpOtDateValue", "CounselorNotesAreCurrentSufficientlyDetailedAndWithinPolicyValue", "IsThereEvidenceOfOngoingAssessmentValue", "IsTAADocumentedIfCoEnrolledValue", "AreAppropriateAttendanceTimesheetsOnFileValue"}, "Attribute", "Value"),
#"Filtered Rows" = Table.SelectRows(#"Unpivoted Only Selected Columns", each ([Value] <> "N/A")),
#"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{"Attribute", "Questions"}, {"Value", "Answers"}, {"Staff.FullName", "Staff"}, {"Region.Title", "Region"}, {"OfYesToNo", "Percent Yes to No"}})
in
#"Renamed Columns"

 

Thanks again.

Hi @Anonymous

 

My apologies. I missed your reply.

 

Please see the attached file. I modified your query.

Please go to Query Editor and see Table1

 

 

 


Regards
Zubair

Please try my custom visuals
Anonymous
Not applicable

Yes! That worked! It took some time but got the job done! Thank you so much! 

Anonymous
Not applicable

Thank you! Smiley Happy

@Anonymous

 

Did it work?


Regards
Zubair

Please try my custom visuals

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.