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

[Marting86]

Hi,

i would like to add a column in my dataset which counts up the repeating invoice numbers. Result should look like this: 

Invoice no.	Count up
1234	1
1234	2
1234	3
1234	4
1234	5
2345	1
2345	2
2345	3
3456	1
3456	2
3456	3
3456	4
…	…

Thank you in advance for your support.

[BA_Pete]

Hi @Marting86 ,

Paste this into a new blank query using Advanced Editor. It gives you both a total count and running count:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNlHSUTJUitWBc4yQOcbIHBNkjimYA2Sbwg2AcoyQORADgGwzuDIoxwiZg6IMaE8sAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Invoice no." = _t, #"Count up" = _t]),
    groupInvNo = Table.Group(Source, {"Invoice no."}, {{"Count", each Table.RowCount(_), Int64.Type}, {"data", each _, type table [#"Invoice no."=nullable text, Count up=nullable text]}}),
    addNestedIndex = Table.TransformColumns(groupInvNo, {"data", each Table.AddIndexColumn(_, "Index", 1, 1)}),
    expandIndex = Table.ExpandTableColumn(addNestedIndex, "data", {"Index"}, {"Index"})
in
    expandIndex

Pete

[Marting86]

Hi Pete,

thank you for the quick response. I think thats generally the right thing, but using your skript its not adding the colums to my existing query. Whats the formula to just add the column "Index" to my existing data? I cant figure it out.

[BA_Pete]

Hi @Marting86 ,

It's not a formula itself, you need to group your data on [Invoice No] first, with an All Rows aggregation column, add the index, then expand the index back out again.

It's not something you can do with just a calculated column as Power Query has no concept of the separate invoice numbers until you group on them.

This step creates the group with an All Rows aggregated column (called [data]) and a count column ([Count]):

groupInvNo = Table.Group(Source, {"Invoice no."}, {{"Count", each Table.RowCount(_), Int64.Type}, {"data", each _, type table [#"Invoice no."=nullable text, Count up=nullable text]}}),

Then, this step adds an index column ([Index]) to the nested tables within the All Rows group column:

addNestedIndex = Table.TransformColumns(groupInvNo, {"data", each Table.AddIndexColumn(_, "Index", 1, 1)}),

Finally, this step expands the index ([Index]) back out to reinstate all your original rows:

expandIndex = Table.ExpandTableColumn(addNestedIndex, "data", {"Index"}, {"Index"})

Pete

[Marting86]

Sorry, for late reaction. Was to busy with other topics to work on with this in the last days. Tried now your first step in the advanced editor. As its in german I had to change "source" to "quelle". But it says it cant find the column "Invoice number". I have checked it several times. Its written correctly. 

I guess it has something to do with the way how the Data is imported:

Its expanding the table just after the second step:

What do I have to write instead of "source".

Sorry, but i am a complete newbie here.

Thx in advance

[BA_Pete]

Hi @Marting86 ,

It looks like you need to promote your table headers first. Go to the Transform tab > 'Use First Row as Headers'.

Once you've done that, just change "Invoice no." in my code to "Invoice number" to match your actual column name.

Pete

[Marting86]

Hi Pete,

both steps you advise were actually already done, just not visible on my screenshots:

Here the full formula line I have added to advanced editor:

#"Group Invoice number" = Table.Group(Quelle, {"Invoice number"}, {{"Count", each Table.RowCount(_), Int64.Type}, {"data", each _, type table [#"Invoice number"=nullable text, Count up=nullable text]}})

But this is still the outcome:

[BA_Pete]

Hi @Marting86 ,

*EDIT* You answered just at the same time I did! So it's not the first issue.

Can you copy and paste your whole M code into a code window (</> button above) please?

Once I've translated it from Germa, I should be able to add my code into it.

*************************************************************************

My guess is either that your column name has hidden characters e.g. "Invoice number    ", or you remove that column in a step prior to trying to group.

Pete

[Marting86]

let
    Quelle = Excel.Workbook(File.Contents("C:\Users\gemagrz\Saved Games\Desktop\Mappe1.xlsx"), null, true),
    Tabelle1_Sheet = Quelle{[Item="Tabelle1",Kind="Sheet"]}[Data],
    #"Höher gestufte Header" = Table.PromoteHeaders(Tabelle1_Sheet, [PromoteAllScalars=true]),
    #"Geänderter Typ" = Table.TransformColumnTypes(#"Höher gestufte Header",{{"Company", Int64.Type}, {"Division", type text}, {"Facility", type text}, {"Customer order numbe", Int64.Type}, {"Delivery number", Int64.Type}, {"Order line number", Int64.Type}, {"Line suffix", Int64.Type}, {"Invoice number", Int64.Type}, {"Order date", Int64.Type}, {"Requested delivery d", Int64.Type}, {"Confirmed delivery d", Int64.Type}, {"Planned delivery dat", Int64.Type}, {"Invoice date", Int64.Type}, {"Year", Int64.Type}, {"Accounting date", Int64.Type}, {"Customer", type text}, {"Name", type text}, {"Address line 1", type text}, {"Customer group", Int64.Type}, {"Customer type", Int64.Type}, {"Statistics customer", type text}, {"Customer order type", type text}, {"Recipient agreement", type text}, {"Payer", type text}, {"BC Level 1", type text}, {"BC Level 2", type text}, {"BC Level 3", type text}, {"BC Level 4", type text}, {"Currency", type text}, {"Invoice rate", Int64.Type}, {"Currency conversion", Int64.Type}, {"Salesperson", type text}, {"District", type text}, {"Country", type text}, {"Statistics identity", type text}, {"OSBSTD.Statistics identity", type text}, {"Contact method", type text}, {"Buying pattern", type text}, {"Warehouse", type text}, {"Item number", Int64.Type}, {"Item group", Int64.Type}, {"Item type", type text}, {"Product group", type text}, {"Supplier", type text}, {"OSBSTD.Statistics identity_1", type text}, {"OSBSTD.Statistics identity_2", type text}, {"Replaced item number", type text}, {"Line type", Int64.Type}, {"MUC trigger", Int64.Type}, {"Statistical U M", type text}, {"Alternate U M", type text}, {"Sales price unit of", type text}, {"Price origin", Int64.Type}, {"Discount model", type text}, {"Discount campaign", type text}, {"OSBSTD.Discount campaign", type text}, {"OSBSTD.Discount campaign_3", type text}, {"OSBSTD.Discount campaign_4", type text}, {"OSBSTD.Discount campaign_5", type text}, {"OSBSTD.Discount campaign_6", type text}, {"Price adjustment lin", Int64.Type}, {"Standard cost code", Int64.Type}, {"Contribution margin", Int64.Type}, {"Sales campaign", type text}, {"Product variant", type text}, {"Configuration number", Int64.Type}, {"Customer order categ", Int64.Type}, {"Address number", type text}, {"Future rate agreemen", type text}, {"Blanket agreement nu", type text}, {"Delivery schedule", type text}, {"Origin", Int64.Type}, {"Identity type", Int64.Type}, {"Assignment", Int64.Type}, {"Assignment suffix", Int64.Type}, {"Service expense type", Int64.Type}, {"Individual item numb", Int64.Type}, {"Item number  individ", type text}, {"Individual item grou", type text}, {"Service manager", type text}, {"Technician", type text}, {"Error symptom", type text}, {"Error cause", type text}, {"Error class", type text}, {"Action", type text}, {"Action class", type text}, {"Agreement number", type text}, {"Agreement type", Int64.Type}, {"Equipment", type text}, {"Main individual item", type text}, {"Transaction reason", type text}, {"Project number", type text}, {"Project element", type text}, {"Invoiced quantity  b", Int64.Type}, {"Invoiced quantity  s", Int64.Type}, {"Invoiced quantity  a", Int64.Type}, {"OSBSTD.Invoiced quantity  s", Int64.Type}, {"Ordered quantity  ba", Int64.Type}, {"Ordered quantity  sa", Int64.Type}, {"Ordered quantity  al", Int64.Type}, {"Ordered quantity  st", Int64.Type}, {"Gross weight", Int64.Type}, {"Net weight", Int64.Type}, {"Volume", Int64.Type}, {"Line amount  local c", type number}, {"Amount based on sale", Int64.Type}, {"Foreign currency amo", type number}, {"Discount amount 1", Int64.Type}, {"Discount amount 2", Int64.Type}, {"Discount amount 3", Int64.Type}, {"Discount amount 4", Int64.Type}, {"Discount amount 5", Int64.Type}, {"Discount amount 6", Int64.Type}, {"Total discount amoun", Int64.Type}, {"Cost price", Int64.Type}, {"Issued cost amount", Int64.Type}, {"Manufactoring cost a", Int64.Type}, {"Net cost 1", Int64.Type}, {"Net cost 2", Int64.Type}, {"Net cost 3", Int64.Type}, {"Net cost 4", Int64.Type}, {"Net cost 5", Int64.Type}, {"Reserved bonus commi", Int64.Type}, {"Days betw actual pla", Int64.Type}, {"Days between actual", Int64.Type}, {"OSBSTD.Days between actual", Int64.Type}, {"Days between conf an", Int64.Type}, {"Days between deliver", Int64.Type}, {"Number of delivery l", Int64.Type}, {"Number of order line", Int64.Type}, {"Lines with correct q", Int64.Type}, {"OSBSTD.Lines with correct q", Int64.Type}, {"Lines on time  wrong", Int64.Type}, {"Number of lines with", Int64.Type}, {"Number of manually p", Int64.Type}, {"Number of bonus gene", Int64.Type}, {"Number of bonus payi", Int64.Type}, {"Number of commission", Int64.Type}, {"OSBSTD.Number of commission", Int64.Type}, {"Invoice recipient", type text}, {"OSBSTD.Item group", type text}, {"Function area", type text}, {"Assignment type", type text}, {"Main product", type text}, {"Sales department", type text}, {"Business area", type text}, {"Product structure ty", type text}, {"Service", type text}, {"Route", type text}, {"Route departure", Int64.Type}, {"Demand", Int64.Type}, {"Attribute value", type text}, {"OSBSTD.Attribute value", type text}, {"OSBSTD.Attribute value_7", type text}, {"OSBSTD.Attribute value_8", type text}, {"OSBSTD.Attribute value_9", type text}, {"OSBSTD.Attribute value_10", Int64.Type}, {"OSBSTD.Attribute value_11", Int64.Type}, {"OSBSTD.Attribute value_12", Int64.Type}, {"OSBSTD.Attribute value_13", Int64.Type}, {"OSBSTD.Attribute value_14", Int64.Type}, {"Attribute model", type text}, {"Action text", type text}, {"Text", type text}, {"OSBSTD.Text", type text}, {"Customer information", type text}, {"Cash code", type text}, {"Agreement order type", type text}, {"Assignment class", type text}, {"Assignment category", Int64.Type}, {"Actual start date  a", Int64.Type}, {"Actual finish date", Int64.Type}, {"Service expense cate", Int64.Type}, {"Price list", type text}, {"Individual item type", Int64.Type}, {"Owner", type text}, {"Brand", type text}, {"User defined field 1", Int64.Type}, {"User defined field 2", Int64.Type}, {"User defined field 3", type text}, {"User defined field 4", Int64.Type}, {"User defined field 5", type text}, {"User defined field 6", type text}, {"User defined field 7", Int64.Type}, {"User defined field 8", type text}, {"User defined field 9", type text}, {"Delivered not invoic", Int64.Type}, {"Entry date", Int64.Type}, {"Entry time", Int64.Type}, {"Change date", Int64.Type}, {"Change number", Int64.Type}, {"Changed by", type text}, {"Add. Status (First Fix)", type text}, {"Postal code", Int64.Type}, {"City", type text}, {"Technician name", type text}, {"BC Level 1 name", type text}, {"BC Level 2 name", type text}, {"BC Level 3 name", type text}, {"BC Level 4 name", type text}, {"Charge", type any}, {"Charge name", type any}, {"Charge value", type any}, {"charge account", type any}, {"Planned start date", type text}, {"Planned finish date", Int64.Type}}),
    #"Geänderter Typ1" = Table.TransformColumnTypes(#"Geänderter Typ",{{"Planned start date", type date}, {"Planned finish date", type text}}),
    #"Geänderter Typ2" = Table.TransformColumnTypes(#"Geänderter Typ1",{{"Planned finish date", type date}}),
    #"Hinzugefügte benutzerdefinierte Spalte" = Table.AddColumn(#"Geänderter Typ2", "Month Amount", each Date.Month([Planned finish date])-Date.Month([Planned start date])+1),
    #"Gefilterte Zeilen" = Table.SelectRows(#"Hinzugefügte benutzerdefinierte Spalte", each ([Month Amount] >1)),
    #"Hinzugefügte benutzerdefinierte Spalte1" = Table.AddColumn(#"Gefilterte Zeilen", "Duplicate Line", each List.Repeat({""},[Month Amount])),
    #"Erweiterte Duplicate Line" = Table.ExpandListColumn(#"Hinzugefügte benutzerdefinierte Spalte1", "Duplicate Line"),
    #"Hinzugefügte benutzerdefinierte Spalte2" = Table.AddColumn(#"Erweiterte Duplicate Line", "Line amount local c new", each [Line amount  local c] / [Month Amount]),
    #"Hinzugefügte benutzerdefinierte Spalte3" = Table.AddColumn(#"Hinzugefügte benutzerdefinierte Spalte2", "SalesPeriod", each Date.AddMonths([Planned start date], [Month Amount]-1)),
    #"Geänderter Typ3" = Table.TransformColumnTypes(#"Hinzugefügte benutzerdefinierte Spalte3",{{"Invoice number", type text}}),
    #"Group Invoice number" = Table.Group(Quelle, {"Invoice number"}, {{"Count", each Table.RowCount(_), Int64.Type}, {"data", each _, type table [#"Invoice number"=nullable text, Count up=nullable text]}})
in
    #"Group Invoice number"

Like this?

[BA_Pete]

Aaaah...I see.

You're trying to refer back to the source in the group step.

Make the end of your query look like this:

    #"Geänderter Typ3" = Table.TransformColumnTypes(#"Hinzugefügte benutzerdefinierte Spalte3",{{"Invoice number", type text}}),
    //The above step hasn't changed, it's just there for reference

    #"Group Invoice number" = Table.Group(#"Geänderter Typ3", {"Invoice number"}, {{"Count", each Table.RowCount(_), Int64.Type}, {"data", each _, type table [#"Invoice number"=nullable text, Count up=nullable text]}})
    addNestedIndex = Table.TransformColumns(#"Group Invoice number", {"data", each Table.AddIndexColumn(_, "Index", 1, 1)}),
    expandIndex = Table.ExpandTableColumn(addNestedIndex, "data", {"Index"}, {"Index"})
in
    expandIndex

Pete

[Marting86]

I think its time to explain what my initial intention was and why my coded looks how it looks now:

I have Data with one line per invoice. My problem is that I have contracts invoices in there which are charged for more than one period (quartarly, annual, etc.). Consequently if I try to display the invoiced sales per month in a bar chart and have only the invoice date as key dimension for my time axis, it shows me e.g. a amount issued for 11 months in the month it has been invoiced. So the key challenge is to spread the turonover of this invoices over the months creating kind of SalesPeriod column. As I have in my data the columns named "planned start date" and "planned finish date", which describe the full period that has been charged, I have first calculated the "month amount".

Next step was duplicating each line according to the "month amount". 

The approach for the final step was to create a SalesPeriod column adding the count to the "planned start Date":

...and to use the Sales Period for the time axis of my charts.

Therefore I need the count. But maybe you can also give me a hint how I cant solve the whole issue smarter.

Anyway thank you so much for your great support here.

Cheers

Martin

[BA_Pete]

LOL! Ok, this is what we call being at the wrong end of an XY Problem.

I take the blame, I should've asked what you were ACTUALLY trying to do 🙂

Check this out:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("JcvBCQAgDEPRXXoWmkYrOIt0/zWMFHJ5fHKvIVwjSBvG5ZiNGmp0bDGOGrJBdNPvNNX0y4+wqgc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Planned start date" = _t, #"Planned finish date" = _t]),
    chgTypes = Table.TransformColumnTypes(Source,{{"Planned start date", type date}, {"Planned finish date", type date}}),
    
    addMonthList =
    Table.AddColumn(chgTypes, "monthList", each
        List.Distinct(
            List.Transform(
                {Number.From([Planned start date]).. Number.From([Planned finish date])},
                each Date.EndOfMonth(Date.From(_))
            )
        )
    ),
    expandMonthList = Table.ExpandListColumn(addMonthList, "monthList")
in
    expandMonthList

The 'addMonthList' step splits your contract ranges into monthly rows which you can relate to your calendar.

Pete

[Marting86]

Hi Pete,

thank you very much for the alternative. 

But i am running into a problem with your skript with rows which have no planned start / finish date:

Can the skript be expanded, so that only the fille rows are considered?

Thank you in advance.

Cheers

Martin

[BA_Pete]

We can extend this to split your invoice values evenly over each month too, like this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("NYxBCgAhDAP/0rPQNKti3yL+/xsbkUIuMwzZ2xCuEaQ1Y3d8BQuw01TQMSUjJTEeEIKYlegky0In40IIEjc5Pw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Planned start date" = _t, #"Planned finish date" = _t, #"Invoice value" = _t]),
    chgTypes = Table.TransformColumnTypes(Source,{{"Planned start date", type date}, {"Planned finish date", type date}, {"Invoice value", type number}}),
    
    addMonthList =
    Table.AddColumn(chgTypes, "monthList", each
        List.Distinct(
            List.Transform(
                {Number.From([Planned start date]).. Number.From([Planned finish date])},
                each Date.EndOfMonth(Date.From(_))
            )
        )
    ),
    addSplitInvoiceValue = Table.AddColumn(addMonthList, "splitInvoiceValue", each [Invoice value] / List.Count([monthList])),
    expandMonthList = Table.ExpandListColumn(addSplitInvoiceValue, "monthList")
in
    expandMonthList

Pete

[Marting86]

Hi, got for my yesterdays question already a solution. But there is still one thing i cant fix. 

Here my skrip:

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}}),
    #"Neueste eingefügt" = Table.AddColumn(chgTypes, "Neueste", each List.Max({[Planned start date], [Invoice date]}), type date),
    #"Neueste eingefügt1" = Table.AddColumn(#"Neueste eingefügt", "Neueste.1", each List.Max({[Planned finish date], [Invoice date]}), type date),
    #"addSalesPeriod" = Table.AddColumn(#"Neueste eingefügt1", "SalesPeriod", each
        List.Distinct(
            List.Transform(
                {Number.From([Neueste]).. Number.From([Neueste.1])},
                each Date.EndOfMonth(Date.From(_))
            )
        )
    ),
    #"addSplitInvoicedSales" = Table.AddColumn(#"addSalesPeriod", "SplitContractInvoicedSales", each [Net Invoiced Sales] / List.Count([SalesPeriod])),
    #"expandMonthList" = Table.ExpandListColumn(#"addSplitInvoicedSales", "SalesPeriod"),
    #"Geänderter Typ3" = Table.TransformColumnTypes(expandMonthList,{{"SplitContractInvoicedSales", type number}, {"SalesPeriod", type date}, {"Total discount amoun", type number}, {"Charge value", type number}})
in
    #"Geänderter Typ3"

Generally it seems to work but the step #"expandMonthList" creates a format error when loading the Data in the model:

When taking out the step #"expandMonthList" the data load works. 

Whats wrong with it?

[Marting86]

Can someone give me a hint? I am stucked here.

[BA_Pete]

Hi @Marting86 ,

Apologies, I've been away for a week.

Please try this updated code. It should fix both issues:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("NYvBDcAwDAJ34R3JmLZRPYuV/dcoflTicwd0Awu6g1eIkuElcVaDCm7LLEsn919ksGZNW/r6DKShOJPzAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Planned start date" = _t, #"Planned finish date" = _t, #"Invoice value" = _t]),
    repBlankForNull = Table.ReplaceValue(Source,"",null,Replacer.ReplaceValue,{"Planned start date", "Planned finish date"}),
    chgTypes = Table.TransformColumnTypes(repBlankForNull,{{"Planned start date", type date}, {"Planned finish date", type date}, {"Invoice value", 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.EndOfMonth(Date.From(_))
            )
        )
    ),
    addSplitInvoiceValue = Table.AddColumn(addMonthList, "splitInvoiceValue", each
        if [monthList] = null then null else [Invoice value] / List.Count([monthList])),
    expandMonthList = Table.ExpandListColumn(addSplitInvoiceValue, "monthList"),
    chgTypes2 = Table.TransformColumnTypes(expandMonthList,{{"monthList", type date}, {"splitInvoiceValue", type number}})
in
    chgTypes2

Pete

[Marting86]

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

[BA_Pete]

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

[Marting86]

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 😞

[BA_Pete]

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