cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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

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

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

PBI Community Highlights

PBI Community Highlights

Check out what's new in the Power BI Community!

Top Ideas
Users Online
Currently online: 21 members 772 guests
Please welcome our newest community members: