How to add a column which counts up the repeating value in another column?

Hi Pete, 

no reason to aplogise. I am sure you have earned your break. But happy you are back finally to support me 😉

I have incorporated all your extensions, even though I dont have the problem with the empty PlannedStartDate/PlannedFinishDate rows anymore. As there were some unlogic data (PlannedFinishDate>PlannedStartDate) I have added two new colums picking the latest date comparing PlannedFinishDate/PlannedStartDate with InvoiceDate. That was neccessary anyway, to show the turnover finally in the correct month. E.g. if InvoiceDate>PlannedStartDate, I want to start splitting the amount earliest from the month it has been invoiced. But I have been curious and tried out if your new code would also handle the empty ones, if not adding the two new date columns: It would 😉

But unfortunately I still get the Data.FormatError. It stops always when loading the Data from the source at 37MB, even when I just try to load the whole FilterList in Power Query the error message is already displayed:

The error message in English: 

[DataFormat.Error] The specification for a Date value could not be parsed.

Here my whole code again, if needed:

let
    Quelle = Folder.Files("C:\Users\gemagrz\Saved Games\Desktop\OSBSTD"),
    #"Benutzerdefinierte Funktion aufrufen1" = Table.AddColumn(Quelle, "Datei transformieren", each #"Datei transformieren"([Content])),
    #"Umbenannte Spalten1" = Table.RenameColumns(#"Benutzerdefinierte Funktion aufrufen1", {"Name", "Source.Name"}),
    #"Andere entfernte Spalten1" = Table.SelectColumns(#"Umbenannte Spalten1", {"Source.Name", "Datei transformieren"}),
    #"Erweiterte Tabellenspalte1" = Table.ExpandTableColumn(#"Andere entfernte Spalten1", "Datei transformieren", Table.ColumnNames(#"Datei transformieren"(Beispieldatei))),
    #"Gefilterte Zeilen" = Table.SelectRows(#"Erweiterte Tabellenspalte1", each true),
    #"Geänderter Typ" = Table.TransformColumnTypes(#"Gefilterte Zeilen",{{"Invoice date", type text}}),
    #"Geänderter Typ1" = Table.TransformColumnTypes(#"Geänderter Typ",{{"Invoice date", type date}}),
    #"Umbenannte Spalten" = Table.RenameColumns(#"Geänderter Typ1",{{"Line amount  local c", "Net Invoiced Sales"}}),
    #"Geänderter Typ2" = Table.TransformColumnTypes(#"Umbenannte Spalten",{{"Planned start date", type text}, {"Planned finish date", type text}}),
    chgTypes = Table.TransformColumnTypes(#"Geänderter Typ2",{{"Planned start date", type date}, {"Planned finish date", type date}, {"Net Invoiced Sales", type number}}),
    #"add LatestDate1" = Table.AddColumn(chgTypes, "LatestInvoice_vs_PlannesStartDate", each List.Max({[Planned start date], [Invoice date]}), type date),
    #"add LatestDate2" = Table.AddColumn( #"add LatestDate1", "LatestInvoice_vs_PlannesFinishDate", each List.Max({[Planned finish date], [Invoice date]}), type date),
    addMonthList = Table.AddColumn(#"add LatestDate2", "monthList", each
    if [LatestInvoice_vs_PlannesStartDate] = null or [LatestInvoice_vs_PlannesFinishDate] = null then null else
        List.Distinct(
            List.Transform(
                {Number.From([LatestInvoice_vs_PlannesStartDate]).. Number.From([LatestInvoice_vs_PlannesFinishDate])},
                each Date.StartOfMonth(Date.From(_))
            )
        )
    ),
    addSplitInvoiceValue = Table.AddColumn(addMonthList, "splitInvoiceValue", each
        if [monthList] = null then null else [Net Invoiced Sales] / List.Count([monthList])),
    expandMonthList = Table.ExpandListColumn(addSplitInvoiceValue, "monthList"),
    chgTypes2 = Table.TransformColumnTypes(expandMonthList,{{"monthList", type date}, {"splitInvoiceValue", type number}})
    in
    chgTypes2

Hi Marting86,

My guess is that your  #"add LatestDate1" and #"add LatestDate2" steps are populating dates where, in some cases, the [LatestInvoice_vs_PlannesStartDate] date value is greater than the [LatestInvoice_vs_PlannesFinishDate] date value. This will cause errors on these rows as PQ can't create a list in this direction and, therefore, an error can't be parsed as a date type.

Can you try the following please:

- Select your 'expandMonthListStep' in the query

- Select the [monthList] column 

- Go to Home tab > Keep Rows (dropdown) > Keep Errors

- Check the StartDate/FinishDate values as per my guess above

Pete

Have processed your Steps, 1 to 3, but cant do the last one as it displays no list as outcome but this error again:

If click on the button "switch to error" it jumps here:

To varify your theory that the error occurs caused by the #"add LatestDate1" and #"add LatestDate2" steps, I have taken em out and tried the code with the original PlanneStartDate/FinishDate columns:

let
    Quelle = Folder.Files("C:\Users\gemagrz\Saved Games\Desktop\OSBSTD"),
    #"Benutzerdefinierte Funktion aufrufen1" = Table.AddColumn(Quelle, "Datei transformieren", each #"Datei transformieren"([Content])),
    #"Umbenannte Spalten1" = Table.RenameColumns(#"Benutzerdefinierte Funktion aufrufen1", {"Name", "Source.Name"}),
    #"Andere entfernte Spalten1" = Table.SelectColumns(#"Umbenannte Spalten1", {"Source.Name", "Datei transformieren"}),
    #"Erweiterte Tabellenspalte1" = Table.ExpandTableColumn(#"Andere entfernte Spalten1", "Datei transformieren", Table.ColumnNames(#"Datei transformieren"(Beispieldatei))),
    #"Gefilterte Zeilen" = Table.SelectRows(#"Erweiterte Tabellenspalte1", each true),
    #"Geänderter Typ" = Table.TransformColumnTypes(#"Gefilterte Zeilen",{{"Invoice date", type text}}),
    #"Geänderter Typ1" = Table.TransformColumnTypes(#"Geänderter Typ",{{"Invoice date", type date}}),
    #"Umbenannte Spalten" = Table.RenameColumns(#"Geänderter Typ1",{{"Line amount  local c", "Net Invoiced Sales"}}),
    #"Geänderter Typ2" = Table.TransformColumnTypes(#"Umbenannte Spalten",{{"Planned start date", type text}, {"Planned finish date", type text}}),
    chgTypes = Table.TransformColumnTypes(#"Geänderter Typ2",{{"Planned start date", type date}, {"Planned finish date", type date}, {"Net Invoiced Sales", type number}}),
    addMonthList = Table.AddColumn(chgTypes, "monthList", each
    if [Planned start date] = null or [Planned finish date] = null then null else
        List.Distinct(
            List.Transform(
                {Number.From([Planned start date]).. Number.From([Planned finish date])},
                each Date.StartOfMonth(Date.From(_))
            )
        )
    ),
    addSplitInvoiceValue = Table.AddColumn(addMonthList, "splitInvoiceValue", each
        if [monthList] = null then null else [Net Invoiced Sales] / List.Count([monthList])),
    expandMonthList = Table.ExpandListColumn(addSplitInvoiceValue, "monthList"),
    chgTypes2 = Table.TransformColumnTypes(expandMonthList,{{"monthList", type date}, {"splitInvoiceValue", type number}})
    in
    chgTypes2

But that results in the same errors:

Therefore it looks to me like the root cause is already conneceted to the PlannesStartDate/FinishDate columns. But I dont get why the column quality shows no errors then:

Thats really frustrating 😞

Hi Pete,

can the code be expanded, so that the steps starting from "addMonthList" only apply on rows with the following Item Numbers:

In fact I only need to do the whole splitting on these ones. Maybe the issues are cause by the others. Its worth a try.

Hi Marting86,

I think this could be caused by a number of things, but debugging it over the forum is going to be challenging.

If you're able to provide me with a source file with sensitive data removed so that I can use your entire M code I should be able to do it offline for you (providing I can recreate the issue), otherwise I'm not sure we can do it with a hundred to-and-fro's.

Regarding the column quality, this feature only evaluates the first X rows (I think 1,000 by default), so won't identify issues in later rows. I think you can increase this in settings to evaluate the entire column, but be mindful that this will hugely increase the amount of time it takes the feature to provide you with the info you need. You're better off identifying the issues yourself via flag columns and filters.

Pete

Argh, you snuck your post in while I was typing mine!

Yes, should be able to expand to include certain [Item No] values, but would be easier to work on the original dataset as per my other post just now.

Pete

Ok, lets do as suggested by you in your other post. I will take one of my files which is creating the error while uploading and leave in only the neccessary columns. What will be the best way to provide it to you?

If you're happy for it to be public, I think you should be able to drag and drop it into a post on here. If that doesn't work, then you could share it from OneDrive/Google Docs/ DropBox and post the link here.

Pete

Can you use that Pete? 

Tell me please when you have downloaded it, so I can take it down again.

Okay, downloaded. You can delete post.

I'll have a look later and let you know how I get on.

Pete

Thank you very much.

Okay, so the issue was a fairly simple one: you had some dates that were zeroes:

I've added a simple replace step at the start to negate this and tidied up the code overall. You'll need to add your source steps at the top (I think everything up to #"Gefilterte Zeilen"?) but should work perfectly now.

You'll notice that I put your column rename step right at the end of the query - I always recommend to do this so that if you want to change column names in the future it doesn't break your whole query.

let
    Source = yourSourceSteps,

    repDateZeroToNull = Table.ReplaceValue( yourLastSourceStep ,0,null,Replacer.ReplaceValue,{"Invoice date", "Planned start date", "Planned finish date"}),
    chgDateTypesToText = Table.TransformColumnTypes(repDateZeroToNull,{{"Invoice date", type text}, {"Planned start date", type text}, {"Planned finish date", type text}}),
    chgDateTypesToDate = Table.TransformColumnTypes(chgDateTypesToText,{{"Invoice date", type date}, {"Planned start date", type date}, {"Planned finish date", type date}}),
    addMonthList = Table.AddColumn(chgDateTypesToDate, "monthList", each
    if [Planned start date] = null or [Planned finish date] = null then null else
        List.Distinct(
            List.Transform(
                {Number.From([Planned start date]).. Number.From([Planned finish date])},
                each Date.StartOfMonth(Date.From(_))
            )
        )
    ),
    addSplitInvoiceValue = Table.AddColumn(addMonthList, "splitInvoiceValue", each
        if [monthList] = null then null else [Line amount  local c] / List.Count([monthList])),
    expandMonthList = Table.ExpandListColumn(addSplitInvoiceValue, "monthList"),
    chgNewColTypes = Table.TransformColumnTypes(expandMonthList,{{"monthList", type date}, {"splitInvoiceValue", type number}}),
    renameCols = Table.RenameColumns(chgNewColTypes,{{"Line amount  local c", "Net Invoiced Sales"}})
in
    renameCols

Regarding limiting the item codes that the split is applied to, there's a couple of ways to go about this. It really depends whether you want the item code list hardcoded into the query, or whether you will want to easily add/remove item codes in future.

To hardcode, you would use this 'addMonthList' step:

addMonthList = Table.AddColumn(chgDateTypesToDate, "monthList", each
    if [Planned start date] = null
        or [Planned finish date] = null
        or not List.Contains({"630", "100", "620"}, [Item number]) then null else
        List.Distinct(
            List.Transform(
                {Number.From([Planned start date]).. Number.From([Planned finish date])},
                each Date.StartOfMonth(Date.From(_))
            )
        )
    ),

To be able to more easily change the values in future, you can create a separate query which is just a list of [Item number] values to include, then replace the List.Contains section above like this:

//Change this:
List.Contains({"630", "100", "620"}, [Item number])

//to this:
List.Contains( nameOfYourListQuery , [Item number])

Pete