Forum Discussion
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:
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.
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
Anonymous
Did it work?
9 Replies
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 Custom6Now 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 PromotedHeadersHello 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
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,
DaleI 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
