Need help or explanation with Custom Function

[Iloveexcel&excelloveme]

Morning All,

I need some help on a custom function I used to clean up a data set (Basically is used to expand Binary and promote the headers).

The scenario is:
1) I have these .txt files that are dump into this folder on a weekly basis
2) I need to append the files together and remove duplicates

(That simple. I achieve the same results by append all the tables without using custom function too but I thought of expanding my M Code and give it a try.

Here the Problem when I'm using custom functions to expand the Binary. All the files carry similar information from the file that I used to build the Custom Function.

If you needed, I can post my code here. Let me know folks

Many Thanks

 

[AliGW]

You need to post a sample file and the code - there is no way we can help you without them.

 

[Iloveexcel&excelloveme]

Sorry about that. Let me prep the file and post it on here later today. There quite a few sensitive data

 

[Iloveexcel&excelloveme]

This is the folder that stored all the weekly files

 

[Iloveexcel&excelloveme]

I can't post my working file i used to do the transformation so i'll post the code instead.

Custom Function

[CODE][/U][/B](TxtFile) =>
let
    Source = Excel.Workbook(File.Contents("C:\Users\Whohahaha\Desktop\Working\Sample\Raw_.15.10.2018.XLSX"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Quant", type text}, {"Material", type text}, {"Pin", type text}, {"Name", type text}, {"Date_0", type text}, {"Date_1", type text}, {"quantity", type text}, {"Date_2", type text}})
in
    #"Changed Type"[B][/B]

[/CODE]

 

[Iloveexcel&excelloveme]

This is the code to append and clean the other files.

When i look at the preview table in "Invoked Custom Function' , all the data are from the Custom function table n not the data from the file itself.
e.g. If in looking at 11/10/2018, i should be seeing data from that table and not from 15/10/2018 which was used to create the custom function

appreciate if anyone can guide me on what went wrong. Thanks

let
    Source = Folder.Files("C:\Users\Whohahaha\Desktop\Working\Sample\1.Weekly"),
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Content", "Name"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Other Columns", "Name", Splitter.SplitTextByDelimiter(".", QuoteStyle.Csv), {"Name.1", "Date", "Month", "Year", "Name.5"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Split Column by Delimiter",{"Name.1", "Name.5"}),
    #"Change Date to number" = Table.TransformColumnTypes(#"Removed Columns1",{{"Date", Int64.Type}, {"Month", Int64.Type}, {"Year", Int64.Type}}),
    #"Added WorkWeek" = Table.AddColumn(#"Change Date to number", "WorkWeek", each Date.WeekOfYear(#date([Year],[Month],[Date]))),
    #"Changed Week to Number" = Table.TransformColumnTypes(#"Added WorkWeek",{{"WorkWeek", Int64.Type}}),
       #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Changed Week to Number", {{"Date", type text}, {"Month", type text}, {"Year", type text}}, "en-US"),{"Date", "Month", "Year"},Combiner.CombineTextByDelimiter("/", QuoteStyle.None),"File_dates"),
    #"Invoked Custom Function" = Table.AddColumn(#"Merged Columns", "Table", each fCleaning_Excel([Content])),
    #"Expanded Table" = Table.ExpandTableColumn(#"Invoked Custom Function", "Table", {"Quant", "Material", "Pin", "Name", "Date_0", "Date_1", "quantity", "Date_2"}, {"Quant", "Material", "Pin", "Name", "Date_0", "Date_1", "quantity", "Date_2"})
in
    #"Expanded Table"

 

[Ken Puls]

To be honest, you don't really need a custom function here, you need an up to date version of Excel 2016. (Right now, you don't have the proper bits to combine files, as you're using the original version of Excel 2016.)

If you open this file: View attachment Sample.xlsx, you will need to:

• Edit the FilesList query --> Source and update it to where the folder lives on your system
• Edit the "Transform Sample File from Transactions" query to do all the things you want to do to each file BEFORE you append them. (This is a sample of one file that will be applied to all of them.)
• Edit the Transactions query to do the things you want to do AFTER the files are appended (Make sure you re-set the data types here as the last step of your query)

Hope this helps!

 

[Iloveexcel&excelloveme]

Thanks @Ken . Yeah, you're right and I'm using the same methods which you posted for my current situation.

But I thought I give custom function a try just so i understand M code better and also why is behaving this way in my use case.

I'm wondering what went wrong, based on my current custom fuction to clean the data, why is it showing only '15.10.2018' data when I applied the steps to the others files.