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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors