Reply
Highlighted
Regular Visitor
Posts: 18
Registered: ‎11-27-2018
Accepted Solution

Rename multiple columns via a function or bulk edit

[ Edited ]

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.


Accepted Solutions
Super User
Posts: 2,838
Registered: ‎09-27-2017

Re: Rename multiple columns via a function or bulk edit

Hi @gtgadmin

 

My apologies. I missed your reply.

 

Please see the attached file. I modified your query.

Please go to Query Editor and see Table1

 

 

 

View solution in original post

Attachment
Super User
Posts: 2,838
Registered: ‎09-27-2017

Re: Rename multiple columns via a function or bulk edit


All Replies
Super User
Posts: 2,838
Registered: ‎09-27-2017

Re: Rename multiple columns via a function or bulk edit

Hi @gtgadmin

 

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

 

 

Attachment
Regular Visitor
Posts: 18
Registered: ‎11-27-2018

Re: Rename multiple columns via a function or bulk edit

[ Edited ]

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.

Super User
Posts: 2,838
Registered: ‎09-27-2017

Re: Rename multiple columns via a function or bulk edit

Hi @gtgadmin

 

My apologies. I missed your reply.

 

Please see the attached file. I modified your query.

Please go to Query Editor and see Table1

 

 

 

Attachment
Community Support Team
Posts: 7,659
Registered: ‎05-02-2017

Re: Rename multiple columns via a function or bulk edit

Hi @gtgadmin,

 

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.
Regular Visitor
Posts: 18
Registered: ‎11-27-2018

Re: Rename multiple columns via a function or bulk edit

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

Regular Visitor
Posts: 18
Registered: ‎11-27-2018

Re: Rename multiple columns via a function or bulk edit

Thank you! Smiley Happy

Super User
Posts: 2,838
Registered: ‎09-27-2017

Re: Rename multiple columns via a function or bulk edit

@gtgadmin

 

Did it work?

Regular Visitor
Posts: 18
Registered: ‎11-27-2018

Re: Rename multiple columns via a function or bulk edit

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