How to add one more field Date into my PBI file ?

[admin11]

Hi All

I have a PBI file , in the past it does not have Date field.

Now i need to add the Date field.

I have try to load the file with out date , the script as below :-

let

    Source = SharePoint.Files("https://isdnholdings.sharepoint.com/sites/FT_AR/", [ApiVersion = 15]),

    #"Filtered Rows" = Table.SelectRows(Source, each ([Name] = "AR_TS_O.csv")),

    #"AR_TS_O csv_https://isdnholdings sharepoint com/sites/FT_AR/Shared Documents/" = #"Filtered Rows"{[Name="AR_TS_O.csv",#"Folder Path"="https://isdnholdings.sharepoint.com/sites/FT_AR/Shared Documents/"]}[Content],

    #"Imported CSV" = Csv.Document(#"AR_TS_O csv_https://isdnholdings sharepoint com/sites/FT_AR/Shared Documents/",[Delimiter=",", Columns=12, Encoding=1252, QuoteStyle=QuoteStyle.None]),

    #"Promoted Headers" = Table.PromoteHeaders(#"Imported CSV", [PromoteAllScalars=true]),

    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Customer/Vendor Code", type text}, {"Customer/Vendor Name", type text}, {"AgeingDate", Int64.Type}, {"A/R amount include GST in LC", type number}, {"AR Invoice Date", type date}, {"AR Invoice Number", Int64.Type}, {"SalesPerson", type text}, {"SlpName", type text}, {"Industry_Description", type text}, {"CustClass", Int64.Type}, {"Payment Terms Code", type text}, {"CreditLine", Int64.Type}})

in

    #"Changed Type"

Since i need to add below Date filed to above script :-

, {"Date", type date}

I get error msg below :-

Hope some one can share with me where go wrong ?

Paul

[Ashish_Mathur]

Hi,

In addition to the change suggested by you, in the #"Imported CSV" step, change 12 to 13.

[admin11]

@Ashish_Mathur 

Thank you very much for sharing , i think you high light the most important point , that must be the reasons , after i have add the Date field it still cannot work. now i will try to change 12 to 13 , see it work ? 

[Anonymous]

HI @admin11,

Did Ashish_Mathur 's suggestion help with your scenario? If that is the case, you can good or accept his suggestion to help others who faced similar requirements.

If the above not help, please share more detailed information to help us clarify and test your situation.

How to Get Your Question Answered Quickly

Regards,

Xiaoxin Sheng

[Anonymous]

Hi @admin11,

In fact, power query steps will cache the data structure into query step and not auto-sync. If you change your data source structure, you need to manually fix them in the query steps to fix not match fields.

Have you refreshed your data table to update for the last version and review each step to confirm if the date field exists in them?
Regards,
Xiaoxin Sheng

[admin11]

@Anonymous 

Thank you for sharing .

Do you mean i need to click on below button " Clear Cache " ? before i can edit the M Code ?

[Anonymous]

HI @admin11,

I mean you can click on the refresh button on the query editor side to refresh the previewed query table records.
Normally, it shows the preview of your query steps operations with the data snapshot from your data source, and they not auto-updated. (sometimes you can find the query table notice these data has stored XXXX day old without update)

Regards,

Xiaoxin Sheng

[admin11]

@Anonymous 

Thank you for your sharing.

Paul

[vanessafvg]

I think you need to explain a bit deeper.  What date field are you trying to add the current date?  Is it a field you want, or a table?  Where is the source of the date field coming from your question does not provide enough information.  what i can see is that its ask where is the date coming from?

[admin11]

@vanessafvg 

Thank you very much for sharing.

My existing raw data file is below , notice that last column no Date field :-

Below is my new raw data file.  The last column is Date field , which i just added . This field will let me me know when this raw data is generated. 

Hope you are clear now.

Paul Yeo