Forum Discussion

steeve695's avatar
steeve695
New Member
9 years ago
Solved

Divide 1 line

How do I divide 1 line into another in a table? I want to divide the selected line into 2 lines. Thank you :)
Need Help
  • MarcelBeug's avatar
    MarcelBeug
    9 years ago

    Voilá:

     

    I added 1 line at the bottom to remove blank lines.

     

    let
    Source = Web.Page(Web.Contents("https://fr.wikipedia.org/wiki/Lyon")),
    Data2 = Source{2}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Data2,{{"Mois", type text}, {"jan.", type text}, {"fév.", type text}, {"mars", type text}, {"avril", type text}, {"mai", type text}, {"juin", type text}, {"jui.", type text}, {"août", type text}, {"sep.", type text}, {"oct.", type text}, {"nov.", type text}, {"déc.", type text}, {"année", type text}}),
    CountNewRows = List.Count(List.Combine(Table.Column(Table.TransformColumns(#"Changed Type",{{Table.ColumnNames(#"Changed Type"){0}, Lines.FromText}}),Table.ColumnNames(#"Changed Type"){0}))),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {}, "Attribute", "Value"),
    LinesFromText = Table.TransformColumns(#"Unpivoted Columns",{{"Value", Lines.FromText}}),
    #"Expanded Value" = Table.ExpandListColumn(LinesFromText, "Value"),
    #"Added Index" = Table.AddIndexColumn(#"Expanded Value", "Index", 0, 1),
    #"Sorted Rows" = Table.Sort(#"Added Index",{{"Attribute", Order.Ascending}, {"Index", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Index"}),
    #"Added Index1" = Table.AddIndexColumn(#"Removed Columns", "Index", 0, 1),
    #"Calculated Modulo" = Table.TransformColumns(#"Added Index1", {{"Index", each Number.Mod(_, CountNewRows), type number}}),
    #"Pivoted Column" = Table.Pivot(#"Calculated Modulo", List.Distinct(#"Calculated Modulo"[Attribute]), "Attribute", "Value"),
    #"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column",{"Index"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns1",Table.ColumnNames(#"Changed Type")),
    #"Removed Blank Rows" = Table.SelectRows(#"Reordered Columns", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null})))
in
    #"Removed Blank Rows"
MarcelBeug's avatar
MarcelBeug
Community Champion
9 years ago

Primary function of Power Query is to get and transform data from many sources, including the web.

So in this case Power Query would be the prefered solution; not DAX.

You can use my code also for data from the web, you only need to adjust the first steps that get the data.

If you can share the URL of the website, then I can adjust my code accordingly.

 

  • MarcelBeug's avatar
    MarcelBeug
    Community Champion
    9 years ago

    Voilá:

     

    I added 1 line at the bottom to remove blank lines.

     

    let
    Source = Web.Page(Web.Contents("https://fr.wikipedia.org/wiki/Lyon")),
    Data2 = Source{2}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Data2,{{"Mois", type text}, {"jan.", type text}, {"fév.", type text}, {"mars", type text}, {"avril", type text}, {"mai", type text}, {"juin", type text}, {"jui.", type text}, {"août", type text}, {"sep.", type text}, {"oct.", type text}, {"nov.", type text}, {"déc.", type text}, {"année", type text}}),
    CountNewRows = List.Count(List.Combine(Table.Column(Table.TransformColumns(#"Changed Type",{{Table.ColumnNames(#"Changed Type"){0}, Lines.FromText}}),Table.ColumnNames(#"Changed Type"){0}))),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {}, "Attribute", "Value"),
    LinesFromText = Table.TransformColumns(#"Unpivoted Columns",{{"Value", Lines.FromText}}),
    #"Expanded Value" = Table.ExpandListColumn(LinesFromText, "Value"),
    #"Added Index" = Table.AddIndexColumn(#"Expanded Value", "Index", 0, 1),
    #"Sorted Rows" = Table.Sort(#"Added Index",{{"Attribute", Order.Ascending}, {"Index", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Index"}),
    #"Added Index1" = Table.AddIndexColumn(#"Removed Columns", "Index", 0, 1),
    #"Calculated Modulo" = Table.TransformColumns(#"Added Index1", {{"Index", each Number.Mod(_, CountNewRows), type number}}),
    #"Pivoted Column" = Table.Pivot(#"Calculated Modulo", List.Distinct(#"Calculated Modulo"[Attribute]), "Attribute", "Value"),
    #"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column",{"Index"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns1",Table.ColumnNames(#"Changed Type")),
    #"Removed Blank Rows" = Table.SelectRows(#"Reordered Columns", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null})))
in
    #"Removed Blank Rows"
    • steeve695's avatar
      steeve695
      New Member
      9 years ago

      Thank you very much :)

      • MarcelBeug's avatar
        MarcelBeug
        Community Champion
        9 years ago

        You're welcome.

         

        Here is a video to illustrate how the code was created.

         

        1 Small correction (not in the video) because the last line from the original table is moved to the top.

        This can be prevented by adding a step after step "Calculated Modulo":

        select column Index, choose "Replace Values", enter 0 as from and 1 as to and then adjust the code and replace 1 by CountNewRows.

         

        = Table.ReplaceValue(#"Calculated Modulo",0,CountNewRows,Replacer.ReplaceValue,{"Index"})

        Edit: or better: the Index that was created before step "Calculated Modulo" should have started at 0 instead of 1.

        Then the original sort order would have been preserved and no correction is required after that step.