Forum Discussion
How to add a column which counts up the repeating value in another column?
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?
Super User
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
Amazing, that worked:
Thank you very much. So I try to go on with the next steps now.
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.
CheersMartin
- 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 expandMonthListThe 'addMonthList' step splits your contract ranges into monthly rows which you can relate to your calendar.
Pete
- 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 expandMonthListPete
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
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?
Can someone give me a hint? I am stucked here.
- BA_Pete
Hi Anonymous ,
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 chgTypes2Pete
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
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 chgTypes2But 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.
- 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
- BA_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?
- BA_Pete
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.
- BA_Pete
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.
- BA_Pete
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 renameColsRegarding 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