Hi guys,
This is a quick question that I have.
I have a table that looks like:
Type Country Date Value Value of A Value of B
A USA 01/01/2017 4 4 null
A CANADA 01/01/2017 6 6 null
A USA 02/01/2017 9 9 null
A CANADA 02/01/2017 10 10 null
...
B USA 01/01/2017 1 null 1
B CANADA 01/01/2017 2.4 null 2.4
B USA 02/01/2017 4 null 4
B CANADA 02/01/2017 1.9 null 1.9
...
I need to extend the values of the column "Value of B", I mean to copy them in block for the same period but in the rows that have values of A, making it look like:
Type Country Date Value Value of A Value of B
A USA 01/01/2017 4 4 1
A CANADA 01/01/2017 6 6 2.4
A USA 02/01/2017 9 9 4
A CANADA 02/01/2017 10 10 1.9
...
B USA 01/01/2017 1 null 1
B CANADA 01/01/2017 2.4 null 2.4
B USA 02/01/2017 4 null 4
B CANADA 02/01/2017 1.9 null 1.9
...
As you can see the values of Type B - USA - 01/01/2017 have been copied to the values of Type A - USA - 01/01/2017, so I am just Extending the values of the block corresponding to Type B to all other Types (in column Values of B).
Can someone help me?
Many thanks!!
Solved! Go to Solution.
In M, see
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUQoNBpEGhvpAZGRgaA7kmCjF6kAknR39HF0w5M3g8lDNRkiSllg0I8sbGoAVOGG32hAuid1qIz0TdO1GGC53wm25HtB5sQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Type = _t, Country = _t, Date = _t, Value = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Type", type text}, {"Country", type text}, {"Date", type date}, {"Value", type number}}), #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Type]), "Type", "Value", List.Sum), #"selected Column"= Table.SelectColumns(#"Changed Type",{"Type"}), #"Removed Duplicates" = Table.Distinct(#"selected Column"), #"Added Custom" = Table.AddColumn(#"Removed Duplicates", "Custom", each #"Pivoted Column" ), #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Country", "Date", "A", "B"}, {"Custom.Country", "Custom.Date", "Custom.A", "Custom.B"}) in #"Expanded Custom"
In M, see
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUQoNBpEGhvpAZGRgaA7kmCjF6kAknR39HF0w5M3g8lDNRkiSllg0I8sbGoAVOGG32hAuid1qIz0TdO1GGC53wm25HtB5sQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Type = _t, Country = _t, Date = _t, Value = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Type", type text}, {"Country", type text}, {"Date", type date}, {"Value", type number}}), #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Type]), "Type", "Value", List.Sum), #"selected Column"= Table.SelectColumns(#"Changed Type",{"Type"}), #"Removed Duplicates" = Table.Distinct(#"selected Column"), #"Added Custom" = Table.AddColumn(#"Removed Duplicates", "Custom", each #"Pivoted Column" ), #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Country", "Date", "A", "B"}, {"Custom.Country", "Custom.Date", "Custom.A", "Custom.B"}) in #"Expanded Custom"
Try creating calculated column:
Value B = IF(Table2[Type]<>"B", LOOKUPVALUE(Table2[Value],Table2[Type],"B",Table2[Country],Table2[Country],Table2[Date],Table2[Date]), //Similar to Excel. Apply a vlookup in the same table where the type is B Table2[Value] )
hi @Omega!
Thanks for your answer, however is not working. I forgot to say that I would need this new column to be created inside the Edit Queries and create it in M language!
Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!
User | Count |
---|---|
119 | |
76 | |
66 | |
51 | |
49 |
User | Count |
---|---|
183 | |
101 | |
80 | |
79 | |
77 |