Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
JerryT
Frequent Visitor

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

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.

JerryT_0-1695371997530.png

and tables looked like this

JerryT_1-1695372255229.png

 

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

1 ACCEPTED SOLUTION
HotChilli
Super User
Super User

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

View solution in original post

2 REPLIES 2
JerryT
Frequent Visitor

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
Super User
Super User

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors