Try to add {"AMOUNT", Int64.Type} after CSV add new field = AMOUNT

[Paulyeo11]

Hi All

My sales order table only have 4 field :-

Below is my PBI file :-

https://www.dropbox.com/s/l0lp1yawkenp73c/M%20CODE%20LOAD%20CSV%20FILE%20V003.pbix?dl=0

My CSV file :-

https://www.dropbox.com/s/kgchgyvgajji617/SI_PM.csv?dl=0

Due to user have new requirement , need to add filed name = AMOUNT

i Try to add , {"AMOUNT", Int64.Type} to below script at last 3 row  , But it cannot work , it cannot detect Amount field :-

let
Source = Folder.Files("C:\Users\pauly\OneDrive - ISDN Holdings Limited\PBI FOLDER\SALES ORDER"),
#"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"CUST_ID", type text}, {"COMPANY", type text}, {"inv", Int64.Type}, {"date", type date}, {"AMOUNT", Int64.Type}})
in
#"Changed Type"

Paul 

[AlB · Accepted solution]

Hi @Paulyeo11 

That {"AMOUNT", Int64.Type} only changes the type of an existing column. So you need to load that column earlier. Is the column available in the data source that you are importing from (like it is in the CSV file you uploaded)? Then you just have to refresh and probably modify some steps in the query to take that new column into consideration. Since it is a relatively simple query, it is probably best to rebuild the steps, starting from where you load the data file

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Paulyeo11]

Hi sir

Thank you for your sharing.

Actually that is sample file , my actual file is having many field , i am concerning that if in future i don't know how to add new field by M code , then it will be very trouble some to reload from zero.

Paul

[AlB]

@Paulyeo11 

You can add a column easily by using a custom column. For a more accurate answer I need more information. Where is the data for the column that you want to add?

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Paulyeo11]

Hi