Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 1 | Company 2 | Company 3 | Company 4 | |
GL 1000 (sum of all entries) | 70000 | 6434 | 77555 | 76576 |
GL 1200 (sum of all entries) | some value | some value | some value | some value |
Thank you for your support
Solved! Go to Solution.
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:
After unpivoting the Balance columns, it would look like this:
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.
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
Proud to be a Super User!
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:
After unpivoting the Balance columns, it would look like this:
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.
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
Proud to be a Super User!
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |