Assistance in creating a recon from two tables having almost similar structure

[ysf]

Hi

I need to compare and recon between two systems having same data and ensure to give a view whee by the results are balancing. Am trying as best as possible not to create many measures since this can lead to possibly extensive maintenance of report at later stage.

see below sample of the source.

the results are to be showing in this way using a matrix table but its not a must to use.

below table reflection of the snippet attachment

table 1		table 2
		segment	segment2				segment	segment2
		title	title	title				title	title
		subtitle	subtitle	subtitle				subtitle	subtitle
		genre	genre	genre				genre	genre
line	line description	inhouse	inflat	Base		line	line description	outhouse	outflat
12	line description 12	1	3	4		12	line description 12	4	7
15	line description 15	65	32	2		15	line description 15	85	45
16	line description 16	43	2	3		16	line description 16	43	2
17	line description 17	87	399	86		17	line description 17	36	876
18	line description 18	98	600	387		18	line description 18	87	368
35	line description 35	24	24	0		35	line description 35	14	24
85	line description 85	87	5000	1888		85	line description 85	36	688
Reckon table
		segment	segment2
line	line	table 1	table 2	recon tabl1 vs table2 diff	table 1	table 2	recon tabl1 vs table2 diff
12	line description 12	1	4	-3	7	7	0
15	line description 15	65	85	-20	34	45	-11
16	line description 16	43	43	0	5	2	3
17	line description 17	87	36	51	485	876	-391
18	line description 18	98	87	11	987	368	619
35	line description 35	24	14	10	24	24	0
85	line description 85	87	36	51	6888	688	6200

[ysf · Accepted solution]

Thanks for the assistance

I have done some lengthy steps in Mquery and managed to get it to work. 
let
Source = Excel.Workbook(File.Contents("Z:\Users\ReconTemplate_Wring and Cagev2.xlsx"), null, true),
#"4.2Dump(BS) Wring_PH_Sheet" = Source{[Item="4.2Dump(BS) Wring_PH",Kind="Sheet"]}[Data],
#"Merged Columns" = Table.CombineColumns(#"4.2Dump(BS) Wring_PH_Sheet",{"Column2", "Column1"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"Line and Description"),
#"Transposed Table" = Table.Transpose(#"Merged Columns"),
#"Filled Down" = Table.FillDown(#"Transposed Table",{"Column1"}),
#"Merged Columns2" = Table.CombineColumns(#"Filled Down",{"Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"),
Custom1 = Record.Field(#"Merged Columns2"{1}, "Merged"),
Custom2 = #"Merged Columns2",
#"Removed Columns" = Table.RemoveColumns(Custom2,{"Merged"}),
#"Added Custom7" = Table.AddColumn(#"Removed Columns", "Custom", each "Wring_BS." & [Column1]),
#"Reordered Columns2" = Table.ReorderColumns(#"Added Custom7",{"Custom", "Column1", "Column2", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17", "Column18", "Column19", "Column20", "Column21", "Column22", "Column23", "Column24", "Column25", "Column26", "Column27", "Column28", "Column29", "Column30", "Column31", "Column32", "Column33", "Column34", "Column35", "Column36", "Column37", "Column38", "Column39", "Column40", "Column41", "Column42", "Column43", "Column44", "Column45", "Column46", "Column47", "Column48", "Column49", "Column50", "Column51", "Column52", "Column53", "Column54", "Column55", "Column56", "Column57", "Column58", "Column59", "Column60", "Column61", "Column62", "Column63", "Column64", "Column65", "Column66", "Column67", "Column68", "Column69", "Column70", "Column71", "Column72", "Column73", "Column74", "Column75", "Column76", "Column77", "Column78", "Column79", "Column80", "Column81", "Column82", "Column83", "Column84", "Column85", "Column86"}),
#"Removed Columns1" = Table.RemoveColumns(#"Reordered Columns2",{"Column1"}),
#"Renamed Columns1" = Table.RenameColumns(#"Removed Columns1",{{"Custom", "Column1"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns1",{{"Column2", type text}}),
#"Merged Columns1" = Table.CombineColumns(#"Changed Type",{"Column1", "Column2"},Combiner.CombineTextByDelimiter(" - ", QuoteStyle.None),"Merged"),
#"Transposed Table1" = Table.Transpose(#"Merged Columns1"),
#"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table1", [PromoteAllScalars=true]),
#"Split Column by Delimiter" = Table.SplitColumn(#"Promoted Headers", "Wring_BS.; - ;", Splitter.SplitTextByEachDelimiter({";"}, QuoteStyle.Csv, true), {"; - ;.1", "; - ;.2"}),
#"Renamed Columns" = Table.RenameColumns(#"Split Column by Delimiter",{{"; - ;.2", "Line"}, {"; - ;.1", "Line Description"}}),
#"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{\\Plenty columns renamed in this step}),
#"Replaced Value" = Table.ReplaceValue(#"Reordered Columns","LINE ","",Replacer.ReplaceText,{"Line"}),
#"Added Custom" = Table.AddColumn(#"Replaced Value", "Line Header", each Custom1),
#"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Line] <> "" and [Line] <> "Balance Sheet")),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Filtered Rows", {"Line", "Line Description", "Line Header"}, "Attribute", "Value"),
#"Split Column by Delimiter3" = Table.SplitColumn(#"Unpivoted Columns", "Attribute", Splitter.SplitTextByEachDelimiter({"."}, QuoteStyle.Csv, false), {"Attribute.1", "Attribute.2"}),
#"Split Column by Delimiter4" = Table.SplitColumn(#"Split Column by Delimiter3", "Attribute.2", Splitter.SplitTextByEachDelimiter({"-"}, QuoteStyle.Csv, false), {"Attribute.2.1", "Attribute.2.2"}),
#"Renamed Columns4" = Table.RenameColumns(#"Split Column by Delimiter4",{{"Attribute.1", "Source System"}, {"Attribute.2.1", "Segment"}, {"Attribute.2.2", "Product"}, {"Value", "Wring Value"}}),
#"Grouped Rows" = Table.Group(#"Renamed Columns4", {"Line", "Segment", "Line Description", "Line Header", "Source System"}, {{"Wring Total", each List.Sum([Wring Value]), type number}}),
#"Inserted Merged Column" = Table.AddColumn(#"Grouped Rows", "Merged", each Text.Combine({[Line], "-", Text.Proper(Text.Trim([Segment]))}), type text),
#"Renamed Columns5" = Table.RenameColumns(#"Inserted Merged Column",{{"Merged", "Line Key"}}),
#"Merged Queries" = Table.NestedJoin(#"Renamed Columns5", {"Line Key"}, #"3 2Cage_BS", {"Line Key"}, "Cage_BS", JoinKind.LeftOuter),
#"Expanded Cage_BS1" = Table.ExpandTableColumn(#"Merged Queries", "Cage_BS", {"Cage Total", "Line Description", "Line Key", "Source System"}, {"Cage Total", "Line Description.1", "Line Key.1", "Source System.1"}),
#"Added Custom1" = Table.AddColumn(#"Expanded Cage_BS1", "Wring vs Cage", each ([Wring Total]) - ([Cage Total])),
#"Reordered Columns1" = Table.ReorderColumns(#"Added Custom1",{"Line", "Segment", "Line Description.1", "Line Description", "Wring Total", "Cage Total", "Line Key", "Line Key.1", "Wring vs Cage"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Reordered Columns1",{{"Wring vs Cage", type number}, {"Cage Total", type text}, {"Wring Total", type text}})
in
#"Changed Type1"

[MahyarTF]

Hi, 

You could do it in Power Query :

1- Merge the table based on the Line column 

2- in the created new table, expand the column and select the 'OutHouse' and 'OutFlat' and deselect the rest

3- Add the Custom Columns and create new columns as you wish :

= Table.AddColumn(#"Expanded Sheet61", "recon T1 vs T2 diff", each [Inhouse] - [Sheet61.OutHouse])

----

= Table.AddColumn(#"Added Custom", "Table1 Seg2", each [Inflat] + [Base])

-----

= Table.AddColumn(#"Reordered Columns", "Recon T1 vs T2 Diff Seg2", each [Table1 Seg2] - [Sheet61.OutFlat])

Reaordr the column by drg and drop and delete the rest column you don't need

This is the sample to create a custom column :

[ysf]

Hi

Thanks for the quick response.

The segments are many, they can be 50 at times and the segment can have 3 sub items in one table and just one or more sub item in the other table. 

[amitchandak]

@ysf , Create common dimesnion for Line and line description and then create measure and take diff

Distinct(Union(

summarize(Table1, Table1[Line ID] , Table1[Line description]) ,

summarize(Table2, Table2[Line ID] , Table2[Line description])

))

Join with both tables

have measure likes

sum(Table1[outhouse]) - Sum(Table2[outhouse])

https://amitchandak.medium.com/power-bi-when-i-asked-you-to-create-common-tables-a-quick-dax-solutio...