Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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.
Solved! Go to Solution.
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?
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
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
yes, unfurtunately the newest version has a bug and it cuts off all the column names and that is why I used version 14.
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
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
Yes! That worked! It took some time but got the job done! Thank you so much!
Thank you!
@Anonymous
Did it work?
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.