Forum Discussion
Store SUM from table 1 in table 2
Super UserQ1: Edit queries and unpivot columns (in the transform tab). That can switch columns with rows.
Q2: build relationship between two tables, then you can use calculate(sum(amount), week="week1"). I am also a new users and hope that can help you.
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"
- ryan_mayu
Q1: I am not sure what kind of DAX you added, but if you add some calculated column, you may not be able to unpivot columns.
Q2: What I can think about is you try to create another new table. One way is "new table = old table". The other way is to right clik and create a reference table. Then you use group by function, group by week column and sum by amount column. Then you will get the total amount for week 1, week2, week3....
Hope that is helpful
- ryan_mayu
Could you please provide me your email address? I create a simple sample for you. Hope that will be helpful.
