Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Good day this depends on our department going the Powerbi route or Qlik sense
So its very important
Please can you tell me explain what was done here in Powerbi il implement it in Qlik sense
ODS Sourcing is just a table load
Cost centre is a excel file
Expanded cost centre is just fields in the cost centre file
Merged queries creates a new table Combined account code
GL account is just a excel file
Expanded GL Account centre are just fields in the GL excel file
Consigno is another data source
Afr view il find out what that is and update my question but you can leave that part out for now
Just tell me what to concatenate , left join , right join , inner join from these files to create this Combined account code table il do the rest .
let
Source = Table.NestedJoin(#"ODS Sourcing", {"CostCentre"}, #"Cost Centre", {"CostCentre"}, "Cost Centre", JoinKind.LeftOuter),
#"Expanded Cost Centre" = Table.ExpandTableColumn(Source, "Cost Centre", {"BU", "BU_Desc", "BUSUB", "BUSUB_Desc", "Company_Div", "Company_Div_Desc", "CostCentre_Desc", "Segm_L1", "Segm_L1_Desc", "Segm_L2", "Segm_L2_Desc", "Segm_L3", "Segm_L3_Desc", "Segm_L4", "Segm_L4_Desc", "Segm_L5", "Segm_L5_Desc", "Segm_L6", "Segm_L6_Desc", "Segm_L7", "Segm_L7_Desc"}, {"BU", "BU_Desc", "BUSUB", "BUSUB_Desc", "Company_Div", "Company_Div_Desc", "CostCentre_Desc", "Segm_L1", "Segm_L1_Desc", "Segm_L2", "Segm_L2_Desc", "Segm_L3", "Segm_L3_Desc", "Segm_L4", "Segm_L4_Desc", "Segm_L5", "Segm_L5_Desc", "Segm_L6", "Segm_L6_Desc", "Segm_L7", "Segm_L7_Desc"}),
#"Merged Queries" = Table.NestedJoin(#"Expanded Cost Centre", {"CombinedAccountCode"}, #"GL Account", {"Account Code"}, "GL Account", JoinKind.LeftOuter),
#"Expanded GL Account" = Table.ExpandTableColumn(#"Merged Queries", "GL Account", {"Account Code", "Account Code_Description", "Exco Code", "Exco_Description", "Exco Note 1 Code", "Exco Note 1_Description", "Exco Note 2 Code", "Exco Note 2_Description", "Exco Note 3 Code", "Exco Note 3_Description", "ExcoSum Code", "ExcoSum_Description", "Segment BSIS1", "Segment BSIS1_Description", "Segment BSIS2", "Segment BSIS2_Description", "Segment IS3", "Segment IS3_Description", "Segment IS4", "Segment IS4_Description", "Segment IS5", "Segment IS5_Description"}, {"Account Code", "Account Code_Description", "Exco Code", "Exco_Description", "Exco Note 1 Code", "Exco Note 1_Description", "Exco Note 2 Code", "Exco Note 2_Description", "Exco Note 3 Code", "Exco Note 3_Description", "ExcoSum Code", "ExcoSum_Description", "Segment BSIS1", "Segment BSIS1_Description", "Segment BSIS2", "Segment BSIS2_Description", "Segment IS3", "Segment IS3_Description", "Segment IS4", "Segment IS4_Description", "Segment IS5", "Segment IS5_Description"}),
#"Merged Queries1" = Table.NestedJoin(#"Expanded GL Account", {"CostCentre"}, Consigno, {"Cost Centre"}, "Consigno", JoinKind.LeftOuter),
#"Merged Queries2" = Table.NestedJoin(#"Merged Queries1", {"CostCentre"}, #"Cost Centre AFR view", {"Cost Centre.2"}, "Cost Centre AFR view", JoinKind.LeftOuter),
#"Expanded Consigno" = Table.ExpandTableColumn(#"Merged Queries2", "Consigno", {"CC - Consigno Cost Centre Owner"}, {"CC - Consigno Cost Centre Owner"}),
#"Expanded Cost Centre AFR view" = Table.ExpandTableColumn(#"Expanded Consigno", "Cost Centre AFR view", {"Country.2", "Overlay"}, {"Country.2", "Overlay"})
in
#"Expanded Cost Centre AFR view"
Hi @vvvvvvizard,
Is it possible to share the sample data with, as it is a little confusing to understand the scenario?