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

@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
Microsoft

@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

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
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors