none
Chart and Data Layout Query RRS feed

  • Question

  • Hi, I am new to Power BI, and have gone through a few tutorials on the same. I need some help on a certain topic related to charting from a data-set which is in this format (screenshot below). In most of the examples for charting, the date column is usually given in a column and is in a recognizable date format. However, I have a data-set whereby dates are mentioned in rows instead and is in "Q4/17" format. The data actually represents quarterly financials for certain companies (operators). Please guide me in preparing as well as laying out the data in a format so that I can keep on updating this on a regular basis.

    Thanks in advance,

    RB_Quest

    Market country Company KPI Q4/17 Q1/18 Q2/18
    IND Ghana Airtel, GH Capex, Fixed                -                  -                  -  
    IND Ghana Airtel, GH CapEx, Wireless               34               37               39
    IND Ghana Airtel, GH Operating revenues             194             213             222
    IND Ghana Airtel, GH Operating revenues, Fixed                -                  -                  -  
    IND Ghana Airtel, GH Operating Revenues, Wireless             194             213             222
    IND Ghana Airtel, GH Total CAPEX (w/o licenses, non-IFRS)               34               37               39
    MEA Ghana Airtel, GH Capex, Fixed                -                  -                  -  
    MEA Ghana Airtel, GH CapEx, Wireless                 4                 3                 3
    MEA Ghana Airtel, GH Operating revenues               39               68               70
    MEA Ghana Airtel, GH Operating revenues, Fixed                -                  -                  -  
    MEA Ghana Airtel, GH Operating Revenues, Wireless               39               68               70
    MEA Ghana Airtel, GH Total CAPEX (w/o licenses, non-IFRS)                 4                 3                 3
    MEA Kenya Airtel, KE Capex, Fixed                -                  -                  -  
    MEA Kenya Airtel, KE CapEx, Wireless                 3                 3                 3
    MEA Kenya Airtel, KE EBITDA (adjusted)                -                  -                  -  
    MEA Kenya Airtel, KE Operating revenues               40               47               54
    MEA Kenya Airtel, KE Operating revenues, Fixed                -                  -                  -  
    MEA Kenya Airtel, KE Operating Revenues, Wireless               40               47               54
    MEA Kenya Airtel, KE Total CAPEX (w/o licenses, non-IFRS)                 3                 3                 3
    MEA Kenya Airtel, KE Wireless EBITDA                -                  -                  -  
    MEA Nigeria Airtel, NG Capex, Fixed                -                  -                  -  
    MEA Nigeria Airtel, NG CapEx, Wireless               28               23               23
    MEA Nigeria Airtel, NG Operating revenues             342             205             216
    MEA Nigeria Airtel, NG Operating revenues, Fixed                -                  -                  -  
    MEA Nigeria Airtel, NG Operating Revenues, Wireless             342             205             216
    MEA Nigeria Airtel, NG Total CAPEX (w/o licenses, non-IFRS)               28               23               23

    Tuesday, December 11, 2018 7:12 AM

Answers

  • Here's more reliable solution (for any other periods too):

    let
        text_cols = {"Market", "country", "Company", "KPI"},
        num_cols = List.RemoveMatchingItems(Table.ColumnNames(Source), text_cols),
        Source = Excel.CurrentWorkbook(){[Name="data"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source, List.Transform (num_cols, each {_, type number})),
        #"Replaced Errors" = Table.ReplaceErrorValues(#"Changed Type", List.Transform (num_cols, each {_, null})),
        #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Replaced Errors", text_cols, "Period", "Sum"),
        #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Columns", "Period", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), {"Quarter", "Year"}),
        transform = Table.TransformColumns(#"Split Column by Delimiter", {{"Quarter", each Number.From(Text.End(_,1))*3}, {"Year", each "20" & _}}),
        #"Added Custom" = Table.AddColumn(transform, "EndOfQuarter", each Date.EndOfQuarter(Date.From("01." & Text.From([Quarter]) & "." & [Year])), type date),
        #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Quarter", "Year"})
    in
        #"Removed Columns"

    Tuesday, December 11, 2018 10:12 AM
  • Hi

    Not sure what date/quarter/year info. you need so kept all. With your data in Table1, your standard columns (market, country…) as the first 4 and the unknown number of quarters following:

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        tableColNames = Table.ColumnNames(Source),
        staticColNames = List.FirstN(tableColNames, 4),
        quartersColNames = List.Skip(tableColNames, 4),
        #"Changed Type" = Table.TransformColumnTypes(Source,
            List.Transform(staticColNames, each {_, type text})),
        #"Changed Type1" = Table.TransformColumnTypes(#"Changed Type",
            List.Transform(quartersColNames, each {_, type number})),
        nullList = List.Transform({1..List.Count(quartersColNames)}, each null),
        #"Replaced Errors" = Table.ReplaceErrorValues(#"Changed Type1",
            List.Zip({quartersColNames, nullList})),
        #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Replaced Errors",
            staticColNames, "Period", "Amount"),
        #"Split QuarterYear" = Table.SplitColumn(#"Unpivoted Columns", "Period",
            Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), {"Quarter", "Year"}),
        #"Transformed Year" = Table.TransformColumns(#"Split QuarterYear",
            {{"Year", (i) => Number.From("20" & i), Int64.Type}}),
        #"Added DateEndOfQuarter" = Table.AddColumn(#"Transformed Year", "Date EndOfQuarter",
            each Date.EndOfQuarter(#date([Year], Number.From(Text.End([Quarter], 1)), 1)), type date),
        tableReordered = Table.SelectColumns(#"Added DateEndOfQuarter",
            List.Combine({{"Year","Quarter","Date EndOfQuarter"}, staticColNames, {"Amount"}}))
    in
        tableReordered

    Output

    Tuesday, December 11, 2018 12:07 PM

All replies

  • Hi,

    To convert data to proper format try this code (Source step need to be changed, sure). Then you may add any visuals.

    let
        Source = Excel.CurrentWorkbook(){[Name="data"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Q4/17", type number}, {"Q1/18", type number}, {"Q2/18", type number}}),
        #"Replaced Errors" = Table.ReplaceErrorValues(#"Changed Type", {{"Q4/17", null}, {"Q1/18", null}, {"Q2/18", null}}),
        #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Replaced Errors", {"Market", "country", "Company", "KPI"}, "Period", "Sum"),
        #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Columns", "Period", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), {"Quarter", "Year"}),
        transform = Table.TransformColumns(#"Split Column by Delimiter", {{"Quarter", each Number.From(Text.End(_,1))*3}, {"Year", each "20" & _}}),
        #"Added Custom" = Table.AddColumn(transform, "Date", each Date.EndOfQuarter(Date.From("01." & Text.From([Quarter]) & "." & [Year]))),
        #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Quarter", "Year"})
    in
        #"Removed Columns"

    Tuesday, December 11, 2018 9:12 AM
  • Here's more reliable solution (for any other periods too):

    let
        text_cols = {"Market", "country", "Company", "KPI"},
        num_cols = List.RemoveMatchingItems(Table.ColumnNames(Source), text_cols),
        Source = Excel.CurrentWorkbook(){[Name="data"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source, List.Transform (num_cols, each {_, type number})),
        #"Replaced Errors" = Table.ReplaceErrorValues(#"Changed Type", List.Transform (num_cols, each {_, null})),
        #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Replaced Errors", text_cols, "Period", "Sum"),
        #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Columns", "Period", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), {"Quarter", "Year"}),
        transform = Table.TransformColumns(#"Split Column by Delimiter", {{"Quarter", each Number.From(Text.End(_,1))*3}, {"Year", each "20" & _}}),
        #"Added Custom" = Table.AddColumn(transform, "EndOfQuarter", each Date.EndOfQuarter(Date.From("01." & Text.From([Quarter]) & "." & [Year])), type date),
        #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Quarter", "Year"})
    in
        #"Removed Columns"

    Tuesday, December 11, 2018 10:12 AM
  • Hi

    Not sure what date/quarter/year info. you need so kept all. With your data in Table1, your standard columns (market, country…) as the first 4 and the unknown number of quarters following:

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        tableColNames = Table.ColumnNames(Source),
        staticColNames = List.FirstN(tableColNames, 4),
        quartersColNames = List.Skip(tableColNames, 4),
        #"Changed Type" = Table.TransformColumnTypes(Source,
            List.Transform(staticColNames, each {_, type text})),
        #"Changed Type1" = Table.TransformColumnTypes(#"Changed Type",
            List.Transform(quartersColNames, each {_, type number})),
        nullList = List.Transform({1..List.Count(quartersColNames)}, each null),
        #"Replaced Errors" = Table.ReplaceErrorValues(#"Changed Type1",
            List.Zip({quartersColNames, nullList})),
        #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Replaced Errors",
            staticColNames, "Period", "Amount"),
        #"Split QuarterYear" = Table.SplitColumn(#"Unpivoted Columns", "Period",
            Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), {"Quarter", "Year"}),
        #"Transformed Year" = Table.TransformColumns(#"Split QuarterYear",
            {{"Year", (i) => Number.From("20" & i), Int64.Type}}),
        #"Added DateEndOfQuarter" = Table.AddColumn(#"Transformed Year", "Date EndOfQuarter",
            each Date.EndOfQuarter(#date([Year], Number.From(Text.End([Quarter], 1)), 1)), type date),
        tableReordered = Table.SelectColumns(#"Added DateEndOfQuarter",
            List.Combine({{"Year","Quarter","Date EndOfQuarter"}, staticColNames, {"Amount"}}))
    in
        tableReordered

    Output

    Tuesday, December 11, 2018 12:07 PM