Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
t-irp
New Member

Newbie question with adding a collection of columns for a financial report.

Hello,

I am new to this and trying my best to learn while doing my day job which is another set of IT disciplines. I am hoping to be pointed in the correct direction for the proper terminology or tutorials. I am not asking for someone to do it for me.  I just need to know where to focus please.

 

I have the following working SQL query.

SELECT

    [Company_Code]
    ,[Year]
    ,[GL_Account]
    ,[Cost_Center]
    ,[Begin_Balance] +[Balance_Period1] + [Balance_Period2] as total   // assume adding more...
   , [Company_Code]
FROM [our DB].[dbo].[GL_BALANCE]
where GL_Account = '1000'
and year = '23'

 

I need a report for the Chief Finanicial Officer.  What I want is for him to select a collection of GL Accounts, the year, and a collection of balance periods from 1 to 13.

 

I would need the top header to show each company such as Company 1, Company 2, Company 3, Company 4.

I would like each row to be a GL_account, summing each GL value depending on what the CFO selected.  So, if he picked 

Begin_Balance] +[Balance_Period1] + [Balance_Period2] +[Balance_Period3] + [Balance_Period4]

 

It would add all 5 of those above resulting in a report such as 

Company 1,   Company 2,   Company 3,   ... 

 

 Company 1Company 2Company 3Company 4
GL 1000 (sum of all entries) 7000064347755576576
GL 1200 (sum of all entries)some valuesome valuesome valuesome value
     

 

Thank you for your support

1 ACCEPTED SOLUTION
DataInsights
Super User
Super User

@t-irp,

 

The approach I would take is to unpivot the Balance columns so you can use a simple SUM measure. Here's an example of what the raw data might look like:

 

DataInsights_0-1675957277137.png

 

After unpivoting the Balance columns, it would look like this:

 

DataInsights_1-1675957354601.png

 

I renamed the Begin_Balance column to Balance_Period0 so it will align with the other Balance columns. Then, create a SUM measure, and drop fields into a matrix. I recommend creating a dimension table for each dimension so you can create a star schema. You can convert Year and Period to a date and create a relationship to the date table.

 

DataInsights_2-1675957486125.png

 

Here's the M code (start with the RenameColumns step):

 

let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "dVAxEsAgCPuLswOgVDv2HZ7//0YRavWsXRL0kkgsxaHzjoCCEAKA0IVjRkVSDILVbx0kkHXkBtHkqzpO+abVczLK8M2PU74FNzwM2eS0L8CvSF8QPP8N7QoT9/2zmnhVr/s3t3L/rNArbEy9Qn7aMoyIWm8=", 
          BinaryEncoding.Base64
        ), 
        Compression.Deflate
      )
    ), 
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [
        Company_Code = _t, 
        Year = _t, 
        GL_Account = _t, 
        Cost_Center = _t, 
        Begin_Balance = _t, 
        Balance_Period1 = _t, 
        Balance_Period2 = _t, 
        Balance_Period3 = _t
      ]
  ), 
  ChangeType = Table.TransformColumnTypes(
    Source, 
    {
      {"Company_Code", Int64.Type}, 
      {"Year", Int64.Type}, 
      {"GL_Account", type text}, 
      {"Cost_Center", type text}, 
      {"Begin_Balance", Int64.Type}, 
      {"Balance_Period1", Int64.Type}, 
      {"Balance_Period2", Int64.Type}, 
      {"Balance_Period3", Int64.Type}
    }
  ), 
  RenameColumns = Table.RenameColumns(ChangeType, {{"Begin_Balance", "Balance_Period0"}}), 
  UnpivotColumns = Table.UnpivotOtherColumns(
    RenameColumns, 
    {"Company_Code", "Year", "GL_Account", "Cost_Center"}, 
    "Attribute", 
    "Value"
  ), 
  RenameColumns2 = Table.RenameColumns(
    UnpivotColumns, 
    {{"Attribute", "Period"}, {"Value", "Amount"}}
  ), 
  RemoveLeadingText = Table.ReplaceValue(
    RenameColumns2, 
    "Balance_Period", 
    "", 
    Replacer.ReplaceText, 
    {"Period"}
  ), 
  ChangeTypePeriod = Table.TransformColumnTypes(RemoveLeadingText, {{"Period", Int64.Type}})
in
  ChangeTypePeriod

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

1 REPLY 1
DataInsights
Super User
Super User

@t-irp,

 

The approach I would take is to unpivot the Balance columns so you can use a simple SUM measure. Here's an example of what the raw data might look like:

 

DataInsights_0-1675957277137.png

 

After unpivoting the Balance columns, it would look like this:

 

DataInsights_1-1675957354601.png

 

I renamed the Begin_Balance column to Balance_Period0 so it will align with the other Balance columns. Then, create a SUM measure, and drop fields into a matrix. I recommend creating a dimension table for each dimension so you can create a star schema. You can convert Year and Period to a date and create a relationship to the date table.

 

DataInsights_2-1675957486125.png

 

Here's the M code (start with the RenameColumns step):

 

let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "dVAxEsAgCPuLswOgVDv2HZ7//0YRavWsXRL0kkgsxaHzjoCCEAKA0IVjRkVSDILVbx0kkHXkBtHkqzpO+abVczLK8M2PU74FNzwM2eS0L8CvSF8QPP8N7QoT9/2zmnhVr/s3t3L/rNArbEy9Qn7aMoyIWm8=", 
          BinaryEncoding.Base64
        ), 
        Compression.Deflate
      )
    ), 
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [
        Company_Code = _t, 
        Year = _t, 
        GL_Account = _t, 
        Cost_Center = _t, 
        Begin_Balance = _t, 
        Balance_Period1 = _t, 
        Balance_Period2 = _t, 
        Balance_Period3 = _t
      ]
  ), 
  ChangeType = Table.TransformColumnTypes(
    Source, 
    {
      {"Company_Code", Int64.Type}, 
      {"Year", Int64.Type}, 
      {"GL_Account", type text}, 
      {"Cost_Center", type text}, 
      {"Begin_Balance", Int64.Type}, 
      {"Balance_Period1", Int64.Type}, 
      {"Balance_Period2", Int64.Type}, 
      {"Balance_Period3", Int64.Type}
    }
  ), 
  RenameColumns = Table.RenameColumns(ChangeType, {{"Begin_Balance", "Balance_Period0"}}), 
  UnpivotColumns = Table.UnpivotOtherColumns(
    RenameColumns, 
    {"Company_Code", "Year", "GL_Account", "Cost_Center"}, 
    "Attribute", 
    "Value"
  ), 
  RenameColumns2 = Table.RenameColumns(
    UnpivotColumns, 
    {{"Attribute", "Period"}, {"Value", "Amount"}}
  ), 
  RemoveLeadingText = Table.ReplaceValue(
    RenameColumns2, 
    "Balance_Period", 
    "", 
    Replacer.ReplaceText, 
    {"Period"}
  ), 
  ChangeTypePeriod = Table.TransformColumnTypes(RemoveLeadingText, {{"Period", Int64.Type}})
in
  ChangeTypePeriod

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.