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

Shape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.

Reply
cinsbox
Frequent Visitor

How to switch data source from .csv to SQL

Hello I am having issue changing data source that currently references a csv file to a SQL list.  I tried to change the source out but it is giving issues in telling me that I am missing fields.  

 

The data source that is .csv is: 

let
Source = Csv.Document(File.Contents("C:\Users\q\OneDrive - Harvey Mudd College\Power BI Data Sync from RE NXT\Gifts - v1.CSV"),[Delimiter=",", Columns=32, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Gift ID", type text}, {"Gift Import ID", type text}, {"Gift System Record ID", Int64.Type}, {"Batch Number", type text}, {"Gift Constituent Code", type text}, {"Date Added", type date}, {"Date Changed", type date}, {"Gift Amount", Currency.Type}, {"Gift Code", type text}, {"Gift Date", type date}, {"Gift Status", type text}, {"Gift Status Date", type text}, {"Gift Subtype", type text}, {"Gift Type", type text}, {"Planned Gift ID", type text}, {"Planned Gift Status", type text}, {"Pledge Balance", Currency.Type}, {"Solicitors", type text}, {"Constituent ID", Int64.Type}, {"Primary Addressee", type text}, {"Campaign ID", type text}, {"Campaign Description", type text}, {"Fund ID", type text}, {"Fund Description", type text}, {"Fund Type", type text}, {"Appeal ID", type text}, {"Appeal Description", type text}, {"Alternate Constituency", type text}, {"Fiscal Year", type text}, {"Proposal System Record ID", type text}, {"Proposal Name", type text}, {"Proposal Purpose", type text}}),
#"Added Refresh Date Time" = Table.AddColumn(#"Changed Type", "Refresh Date Time", each DateTimeZone.FixedLocalNow ()),
#"Added Gift Type and Subtype" = Table.AddColumn(#"Added Refresh Date Time", "Gift Type and Pledge Subtype", each if [Gift Type] = "Pledge" and [Gift Subtype] = "Bookable" then "Pledge - Bookable"

else if [Gift Type] = "Pledge" and [Gift Subtype] = "HMC Payroll Deduction" then "Pledge - HMC Payroll Deduction"

else if [Gift Type] = "Pledge" and [Gift Subtype] = "Non-bookable" then "Pledge - Non-Bookable"

else if [Gift Type] = "Pledge" and [Gift Subtype] = "Non-bookable (RCC)" then "Pledge - Non-Bookable (RCC)"

else if [Gift Type] = "Pledge" and [Gift Subtype] = "Phonathon" then "Pledge - Phonathon"

else if [Gift Type] = "Pay-Cash" and [Gift Subtype] = "Bookable" then "PledgePayment - Bookable"

else if [Gift Type] = "Pay-Cash" and [Gift Subtype] = "HMC Payroll Deduction" then "PledgePayment - HMC Payroll Deduction"

else if [Gift Type] = "Pay-Cash" and [Gift Subtype] = "Non-bookable" then "PledgePayment - Non-Bookable"

else if [Gift Type] = "Pay-Cash" and [Gift Subtype] = "Non-bookable (RCC)" then "PledgePayment - Non-Bookable (RCC)"

else if [Gift Type] = "Pay-Cash" and [Gift Subtype] = "Phonathon" then "PledgePayment - Phonathon"

else if [Gift Type] = "Pay-Stock/Property" and [Gift Subtype] = "Bookable" then "PledgePayment - Bookable"

else if [Gift Type] = "Pay-Stock/Property" and [Gift Subtype] = "HMC Payroll Deduction" then "PledgePayment - HMC Payroll Deduction"

else if [Gift Type] = "Pay-Stock/Property" and [Gift Subtype] = "Non-bookable" then "PledgePayment - Non-Bookable"

else if [Gift Type] = "Pay-Stock/Property" and [Gift Subtype] = "Non-bookable (RCC)" then "PledgePayment - Non-Bookable (RCC)"

else if [Gift Type] = "Pay-Stock/Property" and [Gift Subtype] = "Phonathon" then "PledgePayment - Phonathon"

 

else if [Gift Type] = "Pay-Gift-in-Kind" and [Gift Subtype] = "Bookable" then "PledgePayment - Bookable"

else if [Gift Type] = "Pay-Gift-in-Kind" and [Gift Subtype] = "HMC Payroll Deduction" then "PledgePayment - HMC Payroll Deduction"

else if [Gift Type] = "Pay-Gift-in-Kind" and [Gift Subtype] = "Non-bookable" then "PledgePayment - Non-Bookable"

else if [Gift Type] = "Pay-Gift-in-Kind" and [Gift Subtype] = "Non-bookable (RCC)" then "PledgePayment - Non-Bookable (RCC)"

else if [Gift Type] = "Pay-Gift-in-Kind" and [Gift Subtype] = "Phonathon" then "PledgePayment - Phonathon"

 

else if [Gift Type] = "Pay-Other" and [Gift Subtype] = "Bookable" then "PledgePayment - Bookable"

else if [Gift Type] = "Pay-Other" and [Gift Subtype] = "HMC Payroll Deduction" then "PledgePayment - HMC Payroll Deduction"

else if [Gift Type] = "Pay-Other" and [Gift Subtype] = "Non-bookable" then "PledgePayment - Non-Bookable"

else if [Gift Type] = "Pay-Other" and [Gift Subtype] = "Non-bookable (RCC)" then "PledgePayment - Non-Bookable (RCC)"

else if [Gift Type] = "Pay-Other" and [Gift Subtype] = "Phonathon" then "PledgePayment - Phonathon"

 

else if [Gift Type] = "Recurring Gift Pay-Cash" and [Gift Subtype] = "Bookable" then "PledgePayment - Bookable"

else if [Gift Type] = "Recurring Gift Pay-Cash" and [Gift Subtype] = "HMC Payroll Deduction" then "PledgePayment - HMC Payroll Deduction"

else if [Gift Type] = "Recurring Gift Pay-Cash" and [Gift Subtype] = "Non-bookable" then "PledgePayment - Non-Bookable"

else if [Gift Type] = "Recurring Gift Pay-Cash" and [Gift Subtype] = "Non-bookable (RCC)" then "PledgePayment - Non-Bookable (RCC)"

else if [Gift Type] = "Recurring Gift Pay-Cash" and [Gift Subtype] = "Phonathon" then "PledgePayment - Phonathon"


else [Gift Type]),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Gift Type and Subtype",{{"Refresh Date Time", type datetime}})
in
#"Changed Type1"

 

The list that I have constructed using SQL query contains this data source information under advance editor: 

let
Source = Sql.Database("134.173.46.9", "RE7", [Query="SELECT#(lf)GIFT.UserGiftId as 'Gift ID',#(lf)GIFT.IMPORT_ID as 'Gift Import ID',#(lf)GIFT.ID as 'Gift System Record ID',#(lf)GIFT.BATCH_NUMBER as 'Batch Number',#(lf)-- GIFT.CONSTITUENT_CODE as 'Gift Constituent Code',#(lf)t_2.LONGDESCRIPTION as 'Gift Constituent Code',#(lf)GIFT.DATEADDED as 'Date Aded',#(lf)GIFT.DATECHANGED as 'Date Changed',#(lf)GIFT.Amount as 'Gift Amount',#(lf)-- GIFT.GIFT_CODE as Gift_Code,#(lf)t_1.LONGDESCRIPTION as 'Gift Code',#(lf)GIFT.DTE as 'Gift Date',#(lf)-- GIFT.GIFT_STATUS as Gift_Status,#(lf)CASE GIFT.GIFT_STATUS #(lf)WHEN 1 THEN 'Active' WHEN 2 THEN 'Held'#(lf)WHEN 3 THEN 'Terminated' WHEN 4 THEN 'Completed'#(lf)WHEN 5 THEN 'Cancelled' #(lf)ELSE '' END as 'Gift Status',#(lf)GIFT.GiftStatusDate as 'Gift Status Date',#(lf)-- GIFT.GiftSubType as Gift_Subtype,#(lf)TABLEENTRIES.LONGDESCRIPTION as 'Gift Subtype',#(lf)-- GIFT.TYPE as Gift_Type,#(lf)/* Case statement for gift types */#(lf)CASE GIFT.TYPE#(lf)WHEN 1 THEN 'Cash' WHEN 2 THEN 'Pay-Cash'#(lf)WHEN 3 THEN 'MG Pay-Cash' WHEN 8 THEN 'Pledge'#(lf)WHEN 9 THEN 'Stock/Property' WHEN 10 THEN 'Stock/Property (Sold)'#(lf)WHEN 11 THEN 'Pay-Stock/Property' WHEN 12 THEN 'MG Pay-Stock/Property'#(lf)WHEN 13 THEN 'Pay-Stock/Property (Sold)' WHEN 14 THEN 'MG Pay-Stock/Property (Sold)'#(lf)WHEN 15 THEN 'Gift-in-Kind' WHEN 16 THEN 'Pay-Gift-in-Kind'#(lf)WHEN 17 THEN 'MG Pay-Gift-in-Kind' WHEN 18 THEN 'Other'#(lf)WHEN 19 THEN 'Pay-Other' WHEN 20 THEN 'MG Pay-Other'#(lf)WHEN 21 THEN 'Write Off' WHEN 22 THEN 'MG Write Off'#(lf)WHEN 27 THEN 'MG Pledge' WHEN 30 THEN 'Recurring Gift'#(lf)WHEN 31 THEN 'Recurring Gift Pay-Cash' WHEN 32 THEN 'GL Reversal'#(lf)WHEN 33 THEN 'Amendment' WHEN 34 THEN 'Planned Gift'#(lf)ELSE '' END as 'Gift Type',#(lf)GIFT.PlannedGiftID as 'Planned Gift ID',#(lf)-- GIFT.GIFT_STATUS as 'Planned Gift Status',#(lf)CASE GIFT.GIFT_STATUS #(lf)WHEN 1 THEN 'Active' WHEN 2 THEN 'Held'#(lf)WHEN 3 THEN 'Terminated' WHEN 4 THEN 'Completed'#(lf)WHEN 5 THEN 'Cancelled' #(lf)ELSE '' END as 'Planned Gift Status',#(lf)-- Scalar function for pledge balance #(lf)DBO.Query_SingleGiftPledgebalance(GIFT.ID,GIFT.Type) as 'Pledge Balance',#(lf)-- r_1.CONSTITUENT_ID Solicitor_Constit_ID,#(lf)r_1.FIRST_NAME + ' ' + r_1.LAST_NAME as 'Solicitors', #(lf)RECORDS.CONSTITUENT_ID as 'Constituent ID',#(lf)-- RECORDS.FIRST_NAME + ' ' + RECORDS.LAST_NAME as Constit_Name,#(lf)CASE#(lf)WHEN RECORDS.PRIMARY_ADDRESSEE_EDIT = -1#(lf)THEN RECORDS.PRIMARY_ADDRESSEE#(lf)ELSE#(lf)dbo.GetSalutation(RECORDS.PRIMARY_ADDRESSEE_ID,RECORDS.ID,'',0,0,0,getdate())#(lf)END AS 'PRIMARY ADDRESSEE',#(lf)-- RECORDS.ORG_NAME as Constit_Org_Name,#(lf)CAMPAIGN.CAMPAIGN_ID as 'Campaign ID',#(lf)CAMPAIGN.DESCRIPTION as 'Campaign Description',#(lf)FUND.FUND_ID as 'Fund ID',#(lf)FUND.DESCRIPTION as 'Fund Description',#(lf)-- FUND.FUNDTYPE as 'Fund Type',#(lf)t_3.LONGDESCRIPTION as 'Fund Type',#(lf)APPEAL.APPEAL_ID as 'Appeal ID',#(lf)APPEAL.DESCRIPTION as 'Appeal Description', #(lf)-- AttributeTypes.DESCRIPTION as Attribute_Type,#(lf)t_4.LONGDESCRIPTION as 'Alternate Constituency',#(lf)-- at.description as at_type,#(lf)t_5.longdescription as 'Fiscal Year',#(lf)PROPOSAL.ID as 'Proposal System Record ID',#(lf)PROPOSAL.Proposal_Name as 'Proposal Name',#(lf)PROPOSAL.PURPOSE as 'Proposal Purpose'#(lf)#(lf)FROM GIFT#(lf)INNER JOIN RECORDS ON RECORDS.ID = GIFT.CONSTIT_ID -- Gift constituent#(lf)LEFT JOIN GiftSolicitor ON GiftSolicitor.ParentId = GIFT.ID #(lf)LEFT JOIN RECORDS r_1 ON r_1.ID = GiftSolicitor.SolicitorId -- Gift solicitor#(lf)LEFT JOIN TABLEENTRIES ON TABLEENTRIES.TABLEENTRIESID = GIFT.GiftSubType#(lf)LEFT JOIN TABLEENTRIES t_1 ON t_1.TABLEENTRIESID = GIFT.GIFT_CODE#(lf)LEFT JOIN TABLEENTRIES t_2 ON t_2.TABLEENTRIESID = GIFT.CONSTITUENT_CODE#(lf)LEFT JOIN GiftSplit ON GiftSplit.GiftId = GIFT.ID #(lf)LEFT JOIN FUND ON FUND.ID = GiftSplit.FundId#(lf)LEFT JOIN TABLEENTRIES t_3 ON t_3.TABLEENTRIESID = FUND.FUNDTYPE#(lf)LEFT JOIN CAMPAIGN ON CAMPAIGN.ID = GiftSplit.CampaignId#(lf)LEFT JOIN APPEAL ON APPEAL.ID = GiftSplit.AppealId#(lf)LEFT JOIN GiftProposal ON GiftProposal.GiftId = GIFT.ID #(lf)LEFT JOIN PROPOSAL ON PROPOSAL.ID = GiftProposal.ProposalId#(lf)LEFT JOIN GiftAttributes ON GiftAttributes.PARENTID = GIFT.ID#(lf)LEFT JOIN AttributeTypes ON AttributeTypes.ATTRIBUTETYPESID = GiftAttributes.ATTRIBUTETYPESID#(lf)LEFT JOIN TABLEENTRIES t_4 ON t_4.TABLEENTRIESID = GiftAttributes.TABLEENTRIESID#(lf)LEFT JOIN GiftAttributes ga on ga.PARENTID = GIFT.ID #(lf)LEFT JOIN AttributeTypes at on at.ATTRIBUTETYPESID = ga.ATTRIBUTETYPESID#(lf)LEFT JOIN TABLEENTRIES t_5 ON t_5.TABLEENTRIESID = ga.TABLEENTRIESID#(lf)#(lf)WHERE #(lf)(GiftSolicitor.Sequence = 1 OR GiftSolicitor.Sequence IS NULL) -- No solicitor and primary solicitor#(lf)AND AttributeTypes.DESCRIPTION = 'Alternate Constituency'#(lf)AND at.DESCRIPTION = 'Fiscal Year'#(lf)ORDER BY GIFT.ID "])
in
Source

 

I tried to switch out only the first line of Source for the .csv list (bolded), while the syntax under the advanced editor didn't give me error, when refreshing, I get the "The column Gift ID of the table wasn't found."  

 

I'd appreciate if anyone can help.  Thank you.

2 REPLIES 2
v-yanjiang-msft
Community Support
Community Support

Hi @cinsbox ,

Was your problem resolved? If so, could you please accept the helpful post as solution, then we will close the thread and people have similar problem will benefit here.

 

Best regards,

Community Support Team_yanjiang

v-yanjiang-msft
Community Support
Community Support

Hi @cinsbox ,

Based on the error message, please firstly only write the SQL source code"let Source = Sql.Database........in Source" in a blank query and check if the result contains a column named "Gift ID". If not, the error occured because the following steps refering a column "Gift ID" but not exist in the previous step. Then you can tweak the formula like change the column name etc. in the Advanced editor. 

vyanjiangmsft_0-1684227204966.png

What's more, in Power Query, if an error occurs, you can find the first step which has an error in Applied steps, the error must happen in this step.

 

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

Best regards,

Community Support Team_yanjiang

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.