Hello Everybody,
as I am new to this forum I'd like to introduce myself.
Twenty years ago (yes, I am above 50 ...), I was a developer und I used to code a lot with than C++, VB.net, ... so I am sort of familiar with Code-Structure. I thougt.
I am not that new to Power Query, using it here and there for about seven years for some simple tasks. A little bit of M is known to me but I am still a beginner. I have nothing but "ordinary" Power Query advanced editor in Excel, no Power BI Desktop, no Visual Studio.
Now I have the task to get data from files in a Folder (no problem so far), but people sending the data-files often change columen-headers by adding comments, hints, and so on.
Now I thougt why not trying to secure those entries by using a loop to compare a list of lists, containing a search-string and a name-string and then to rename the headers.
Thats my (not really working) code and I don't get my mistake(s):
let
Quelle = Folder.Files(Verzeichnispfad), // Verzeichnispfad is a parameter, containing the path
// Selecting the needed files
ExcelDateien=Table.SelectRows(Quelle, each [Extension] = ".xlsx" and not Text.StartsWith([Name], "~$") and Text.Contains([Name], "SearchString1")),
// Schleife durch alle Excel-Dateien im Verzeichnis
ErsetzteDateien = List.Transform(ExcelDateien[Content], each
let
// File-Loading
ExcelDatei = _,
// trying to extract date of deadline from file-name; not really working ...
Stichtag = Date.FromText(Text.BetweeenDelimiters(ExcelDatei[Name], "(Stichtag",")")),
// Loading the content
Inhalt = Excel.Workbook(ExcelDatei, true),
ErsteTabelle = Inhalt{[Item="Tabelle1", Kind="Table"]}[Data],
//Adding a column named "Stichtag" = Deadline - column added, but trying to fill in the date produces Error
TabelleMitStichtag = Table.AddColumn(ErsteTabelle, "Stichtag", each Stichtag, type date), // just trying; this works
// Looping throug Header-Names and replcing them, if a certain String is found
ErsetzteTabelle = List.Accumulate(L_Rename_SearchString1, TabelleMitStichtag, (tabelle, ersatz) =>
let
ZuErsetzenderName = ersatz{0}, // searchstring
NeuerName = ersatz{1}, //New Headername=constant name
// Trying to find the searchstring
ExistiertSpalte = List.Contains(Table.ColumnNames(tabelle), each Text.Contains(_, ZuErsetzenderName)),
// If SearchString is found, replace HeaderName with constant name
NeueTabelle = if ExistiertSpalte then Table.RenameColumns(tabelle, {{ZuErsetzenderName, NeuerName}}) else tabelle
in
NeueTabelle
)
in
ErsetzteTabelle
),
// creating a cross-table - just playing around ...
Endtabelle = Table.Combine(ErsetzteDateien)
in
Endtabelle
Well, I guess the code is full of errors and a way to help me, I imagine, could be a hint to a tutorial how to learn Coding in M - Language. Like, the problem is - there are so many tutorials out there - but a lot of them are a) related to the use of the GUI, b) not using the simple editor c) not about coding ...
Help with my code is also appreciated ;-).
Sorry for that terrible long post - if this is not the right place, feel free to move the thread.
Have a nice day
Rene
