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

Join 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.

Reply
SSS
Helper I
Helper I

Extend block of values

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!!

1 ACCEPTED SOLUTION
Eric_Zhang
Microsoft Employee
Microsoft Employee

@SSS

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"

M.gif

View solution in original post

3 REPLIES 3
Eric_Zhang
Microsoft Employee
Microsoft Employee

@SSS

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"

M.gif

Omega
Impactful Individual
Impactful Individual

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!

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.