Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
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!
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
64 | |
55 | |
53 | |
36 | |
34 |
User | Count |
---|---|
85 | |
73 | |
55 | |
45 | |
43 |