Forum Discussion

LAH's avatar
LAH
New Member
1 year ago
Solved

Handling Poor Data in Power BI

Hi Everyone,

 

I am very new to Power BI and attempting to teach myself how to use it. I have some very poorly layed out data that I wish to change the format of. Can anyone walk me through how to do this? I could use a python script to ammend to output a cleaner version of the data but I was hoping to instead learn more Power BI functionality. 

 

Here is the format of the origonal data.

 

Algeria
Country risk assessments :
C
Business climate assessments :
C
Angola
Country risk assessments :
C
Business climate assessments :
D
Argentina
Country risk assessments :
D
Business climate assessments :
B


And I want to data to move into columns and look like the following: 

Country     |  Country risk assessments | Business climate assessments |
Algeria       |  C                                      |  C
Angola       |  C                                      |  D
Argentina  |  D                                      |  B


So there are a number of problems to fix here.
1) The multiple "titles" of "Country risk assessments"and "Business climate assessments"
and 
2) Realiging the rows to columns which can't just be a transpose as they are sequential like headings

Any help would be much appreaciated 

Need Help
Tutorial Requests
  • parry2k's avatar
    parry2k
    1 year ago

    LAH start a new blank query and copy this M code, follow these steps in your table, tweak the solution as you see fit.

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcsxJTy3KTFSK1YlWcs4vzSspqlQoyizOVkgsLk4tLs5NzSspVrCCSINJp9LizDygjEJyTmZuYkkqToWOeen5OdQ12AVicFE6UCwzjyizXYg120kpNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Data = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Data", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Added Group" = Table.AddColumn(#"Added Index", "Group", each [Index]-Number.Mod([Index],5), Int64.Type),
    #"Added Column Order" = Table.AddColumn(#"Added Group", "Column Order", each Number.Mod([Index],5), Int64.Type),
    #"Removed Index" = Table.RemoveColumns(#"Added Column Order",{"Index"}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Removed Index", {{"Column Order", type text}}, "en-CA"), List.Distinct(Table.TransformColumnTypes(#"Removed Index", {{"Column Order", type text}}, "en-CA")[#"Column Order"]), "Column Order", "Data"),
    #"Renamed Columns" = Table.RenameColumns(#"Pivoted Column",{{"0", "Country"}, {"2", "Country risk assessments"}, {"4", "Business climate assessments"}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Renamed Columns",{"Country", "Country risk assessments", "Business climate assessments"})
in
    #"Removed Other Columns"

     

    Output:

     

     

1 Reply

  • parry2k's avatar
    parry2k
    Super User
    1 year ago

    LAH start a new blank query and copy this M code, follow these steps in your table, tweak the solution as you see fit.

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcsxJTy3KTFSK1YlWcs4vzSspqlQoyizOVkgsLk4tLs5NzSspVrCCSINJp9LizDygjEJyTmZuYkkqToWOeen5OdQ12AVicFE6UCwzjyizXYg120kpNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Data = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Data", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Added Group" = Table.AddColumn(#"Added Index", "Group", each [Index]-Number.Mod([Index],5), Int64.Type),
    #"Added Column Order" = Table.AddColumn(#"Added Group", "Column Order", each Number.Mod([Index],5), Int64.Type),
    #"Removed Index" = Table.RemoveColumns(#"Added Column Order",{"Index"}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Removed Index", {{"Column Order", type text}}, "en-CA"), List.Distinct(Table.TransformColumnTypes(#"Removed Index", {{"Column Order", type text}}, "en-CA")[#"Column Order"]), "Column Order", "Data"),
    #"Renamed Columns" = Table.RenameColumns(#"Pivoted Column",{{"0", "Country"}, {"2", "Country risk assessments"}, {"4", "Business climate assessments"}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Renamed Columns",{"Country", "Country risk assessments", "Business climate assessments"})
in
    #"Removed Other Columns"

     

    Output: