Problem happens when I try to expand a record as a table

[JerryT]

Hi All,

I'm new to power bi desktop and power query.

Now I have a table named ProjectsSummary which contain 5 columns which will be used below: [PN] [StartDate] [MonthsBetween] [MonthlyQty] [Price]. Every single column's value's not unique in their list.

I'm trying to write this code to establish a new table named SalesOrder to emulate a fake sales order list to visualize it in power bi by month or by year.

let
Source = ProjectsSummary,
#"Changed Type" = Table.TransformColumnTypes(Source,{{"PN", type text}, {"MonthlyQty", Int64.Type}, {"Price", type number}, {"StartDate", type date}, {"MonthesBetween", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "SalesOrder", each
let
PN = [PN],
MonthlyQty = [MonthlyQty],
Price = [Price],
StartDate = [StartDate],
MonthesBetween = [MonthesBetween],
SalesOrder = List.Generate(()=>[i=0, date=StartDate], each [i]<MonthesBetween, each [i=[i]+1, date=Date.AddMonths([date], 1)]),
#"Converted to Table" = Table.FromList(SalesOrder, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"i", "date"}, {"i", "date"}),
#"Added Custom1" = Table.AddColumn(#"Expanded Column1", "MonthlyQty", each MonthlyQty),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "Price", each Price)
in
#"Added Custom2"
),
#"Expanded SalesOrder" = Table.ExpandTableColumn(#"Added Custom", "SalesOrder", {"PN", "MonthlyQty", "Price", "date"}, {"PN", "MonthlyQty", "Price", "date"})
in
#"Expanded SalesOrder"

So, the problem here's that the last line of code's not able to expand, error occurs but I have no reason why.

The result of last step #"Added Custom2" added a new custom column contains the emulated sales order like this.

and tables looked like this

Would anyone please tell me why this code failed and what's the method to fix it, thanks.

[JerryT]

I tried to do the whole work from start again and things worked.

So maybe that's the problem, your column names are not able to be duplicated after expanding.

Thank you for your help sir!

[HotChilli · Accepted solution]

It looks like the column names are wrong in the last step "Expanded SalesOrder".  You can't have names in the embedded table being expanded to names that already exist.

Have you edited this manually because I think if you delete the last step and then expand from the column header, it should create the code automatically and work.

If that doesn't work, please post some sample data and i'll test it