Power Query - Merge two tables to get accurate results per row.

[lennox25]

Hi, Hoping for some help - please.

I have two tables

One of them is for Store Audit Visits below.

The other table is for an Enforcement Visit below. This table will need separating so that Advice Given has its own column and all the other categories are combined into one column.

The expected result is below. Is this possible?

[_AAndrade]

Hi @lennox25,

I'm using some data from your initial post and here is my solution:

1. I created two table like you have: T_Stores and T_StoreDetail.
2. Do same data transformations on the T_StoreDetail table and the final result is this:

The M code is here:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jdKxCoMwEAbgV5HMQi5njDp2KoVuFjqIg9TYZkkhpj5/VaSVJKUZAx//f1yuaQhCXpGUHPpJ3WRyVJPU8xMFhYIiYEba9IOundFK35OztFaan8zJYpwCLohvSIhAYU6B75NCiGVOXRAxCuUeVSyACqcuhLCMQHMX5H+TeAwSESiDCDQLEB6qH09jk9p2wyD7RYmQcheFEQirCATu563oYjq13tRpHF9ym8pngal85J8nfFn7Bg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Store No" = _t, Category = _t, #"Date Sold" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Store No", Int64.Type}, {"Category", type text}, {"Date Sold", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "OtherCat", each if [Category] <> "Advice Given" then "All other Categories" else [Category]),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"Store No", "OtherCat"}, {{"Count", each Table.RowCount(_), Int64.Type}}),
    #"Pivoted Column" = Table.Pivot(#"Grouped Rows", List.Distinct(#"Grouped Rows"[OtherCat]), "OtherCat", "Count")
in
    #"Pivoted Column"

3. After go to T_Stores table and use the merge query option, the final result is this:

The M code is here:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwtVTSUXLPz08BUkZm+gbm+kYGRsZKsTogSTMzoGhwYklmcVpickl+USVIkam+gQmKInOgqGduQVF+WWpual6JQl5qakoqyDxDA30DQ5BSE6hScwuEZYYW+kB5JHMsDTEtMzRHWBYLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Store No" = _t, #"Audit Result" = _t, #"Audit Date" = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Store No", Int64.Type}, {"Audit Result", type text}, {"Audit Date", type date}}),
    MergedQueries = Table.NestedJoin(ChangedType, {"Store No"}, T_StoreDetail, {"Store No"}, "T_StoreDetail", JoinKind.LeftOuter),
    ExpandedT_StoreDetail = Table.ExpandTableColumn(MergedQueries, "T_StoreDetail", {"Advice Given", "All other Categories"}, {"Advice Given", "All other Categories"})
in
    ExpandedT_StoreDetail

[lennox25]

Excellent option  -Thank you so much!

[_AAndrade]

@lennox25 Did my post solve your problem as @samratpbi ?
If so, please also mark my post as a solution, it will help me.
Thank you.

[samratpbi · Accepted solution]

Hi,
I think you dont even need to merge tables in power query. What you can do is join the tables using store No. and bring all the columns from your first table into visual and create 2 measures like below:
Advice Given = 
CALCULATE (
     COUNTROWS( T2),
    T2.Category = "Advice Given"
)
Other Catogories Combined = 
CALCULATE (
     COUNTROWS( T2),
    T2.Category <> "Advice Given"
)

If this resolves your problem then please mark it as solution, Thanks!

[lennox25]

Excellent  -Thank you  - this has easily resolved my issue  🙂