Forum Discussion
Store SUM from table 1 in table 2
Hi Ryan
Thanks for reply.
Q1: For table 1 I have database connection with base data, I then adding DAX calculation columns to do projection and these columns not appearing in Power Querie. It looks like that I have to use either DAX or Power Querie, but not both at the same time.
Q2: calculate(sum(amount), week="week1") - this would sum totals only for Week 1, but how to "tell table 2" that for Week 1 it has to take sum from Week 1 (Table 1), for Week 2 sum from Week 2 (Table 1), for Week 3 sum from Week 3 (Table 1) etc... and all this happens in the same column.
bump
- PattemManohar
Community Champion
Please try this.....
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjQ0MlbSUTI0ABGmQMIIzLIAEWYgrlKsTrSSE1GqnIlS5UKUKlfCqmIB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Part Number" = _t, #"Current Stock" = _t, #"Week 1 Closing stock" = _t, #"Week 2 Closing stock" = _t, #"Week 3 Closing stock" = _t, #"Week 4 Closing stock" = _t, #"Week 5 Closing stock" = _t]), #"Removed Columns" = Table.RemoveColumns(Source,{"Week 5 Closing stock"}), #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"Current Stock", Int64.Type}, {"Week 1 Closing stock", Int64.Type}, {"Week 2 Closing stock", Int64.Type}, {"Week 3 Closing stock", Int64.Type}, {"Week 4 Closing stock", Int64.Type}}), #"Removed Columns1" = Table.RemoveColumns(#"Changed Type",{"Part Number", "Current Stock"}), #"Demoted Headers" = Table.DemoteHeaders(#"Removed Columns1"), #"Changed Type1" = Table.TransformColumnTypes(#"Demoted Headers",{{"Column1", type any}, {"Column2", type any}, {"Column3", type any}, {"Column4", type any}}), #"Transposed Table" = Table.Transpose(#"Changed Type1"), #"Added Custom" = Table.AddColumn(#"Transposed Table", "Total Closing Stock", each [Column2]+[Column3]+[Column4]+[Column5]+[Column6]), #"Removed Columns2" = Table.RemoveColumns(#"Added Custom",{"Column2", "Column3", "Column4", "Column5", "Column6"}), #"Inserted Text Before Delimiter" = Table.AddColumn(#"Removed Columns2", "Text Before Delimiter", each Text.BeforeDelimiter([Column1], " ", 1), type text), #"Reordered Columns" = Table.ReorderColumns(#"Inserted Text Before Delimiter",{"Column1", "Text Before Delimiter", "Total Closing Stock"}), #"Removed Columns3" = Table.RemoveColumns(#"Reordered Columns",{"Column1"}), #"Renamed Columns" = Table.RenameColumns(#"Removed Columns3",{{"Text Before Delimiter", "Weeks"}}) in #"Renamed Columns"Hi pattemmanohar
Where to put this "code" ?
Looks wierd...
Thanks
R
The code that PattemManohar provided you is Power Query code (aka M). Within Power BI Desktop click on 'Edit Queries'. From the resulting page click on 'Advanced Editor'. This will bring up a window where you should place the code provided.
