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.
