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.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors