Handling missing date columns in select files

[ayana]

I'm importing data from hundreds of csv files into Power BI and I have a problem that I hope someone can help me resolve. Some of the files have a date column but others don't. Those that don't have a date column also have unwanted top rows (see image). The files that have a date column have a date range stamped somewhere in the file (cell B10). For these files I want to add a date column, grab the date (01/01/2024) and populate the date column with it. After that I want to remove all unwanted rows (1-17) from but leave all other files intact (those don't need any rows removed). Any suggestions for how to do it in Power Query?

I have attached a sample file for reference. 

[Omid_Motamedise]

To acccess to the datem you can use indexing operation like Source[Column2]{5} meanse table Source Column namely column 2 and row index equal to 5 and for removing unwanted row, use Table.RemoveTopRows function

[dufoq3]

Hi @ayana, check this:

Before

After

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkotyC8qUQguzc1NLKpU0lFCQbE60UrYxEhU7pyTmZpXgk3GJbEktRjIMTDUByIjAyMTBV0FBIco04kVC0otLs0pKabECMd0iD/cMouKSxT8EnNTgRyfRAQ7uDQ5ObW4OK00B4WjYOCkEFKUmFecllqEqgpsqpMzUNDREUg4OQGJSHCQ+OVDmeBwcnUD8gIDgURQEEwSoSIWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t]),
    GrabDate = [ a = List.Select(Table.ToRows(Table.FirstN(Source, each [Column1] <> "Agent")), each _{0} = "Dates"){0}?{1}?,
    b = Date.From(Text.BeforeDelimiter(a, " - "))
  ][b],
    StepBack = Source,
    Filter = Table.PromoteHeaders(Table.Skip(StepBack, each [Column1] <> "Agent")),
    Ad_Date = Table.AddColumn(Filter, "Date", each GrabDate, type date)
in
    Ad_Date

If you want us to help you with csv files - upload at least 2 files i.e. to google drive and provide here a link. (1 file with correct date and another one similar as your sample)

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...