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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Power BI Header Change

Power BI Header Update

Read about the exciting updates for the Power BI forum.

Top Solution Authors
Top Kudoed Authors