cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
SSS Regular Visitor
Regular Visitor

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

Accepted Solutions
Moderator Eric_Zhang
Moderator

Re: Extend block of values

@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
Omega Established Member
Established Member

Re: Extend block of values

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]
			)
SSS Regular Visitor
Regular Visitor

Re: Extend block of values

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!

Moderator Eric_Zhang
Moderator

Re: Extend block of values

@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

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors (Last 30 Days)